Converting excel spreadsheets to tables pre upload | Forum

Resolved · Medium Priority · Version 365

Fri 9 Jun 2023, 11:30 Delegate Nikki said...

Nikki has attended:
Power BI Modelling, Visualisation and Publishing course
Excel Forecasting and Data Analysis course

Converting excel spreadsheets to tables pre upload

When I convert standard Excel spreadsheets to tables before loading into Power BI, when I click "get Data" both the original spreadsheets and the tables are displayed, and I need to re name the tables to the original naming configuration. Is there a way to stop this happening.

Sat 10 Jun 2023, 21:25 Trainer Jens said...

RE: converting excel spreadsheets to tables pre upload

Hi Nikki,

Thank you for the forum question.

You can reference worksheets or Excel table by name or index numbers. The first table will index 0, the next index 1 and so on.

If you reference the table by index numbers, they will not need to have the same names and you will not need to rename the tables.

I will need to have more details to give you a solution. I guess you have created a connection to a table and then you want to replace the data with new data from a table with another name or do you want to append data or merge data. Please provide me with more details.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 12 Jun 2023, 11:16 Delegate Nikki said...

RE: converting excel spreadsheets to tables pre upload

Hi Jens, thanks for the response, I have tried to attach a screen shot, but it doesn't appear to allow this. Basically if I have an Excel spreadsheet titled "Accident" when I convert this to a table and then click "Get data" it shows me both documents:
Accidents spread sheet and Table 1 (which is same data but in table format.) so i then need to select Table 1 but then rename to Accidents within report.
hope that makes sense

Nikki

Mon 12 Jun 2023, 11:41 Trainer Jens said...

RE: converting excel spreadsheets to tables pre upload

Hi Nikki,

When you connect to a table the table name in Power BI will get the table name from Excel. If you name the table in Excel "Accident" then when you connect to the table it will also be named "Accident" in Power BI.

You have the option to connect both to the table and the worksheet. If the worksheet is named "Accident" then the table in Power BI will be named "Accident", but then your data must start from A1, otherwise you will have to do some cleaning in the query editor.

If this is not what you are looking for, you can send screenshots to: info@stl-training.co.uk

Please write in the subject that the email is for me thanks.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Master DAX (Data Analysis Expressions)

DAX is the formula language used in Power BI for creating custom calculations and aggregations. Invest time in learning DAX to unlock the full potential of Power BI. Understand functions like CALCULATE, FILTER, and SUMX, as they are fundamental for creating complex calculations and measures.

View all Power BI hints and tips