Re: The Day I Been Dreading

Anonymous · ‎05-16-2021

I knew this day would come: I have a source file (Excel) that I get every week and load to Power BI. Two new columns have been added to this file, and will be included in all files going forward. I need these columns for reporting. How do I successfully refresh this file and bring the new columns into my reporting?

Thank you,

PaulDBrown · ‎05-16-2021

Select the whole data range in the file and make it a table (ctrl + t). Reference this table in the import phase in PQ (you can create a new query to import the table and then copy the relevant M Code and substitute the corresponding M code in the original query)

By importing a table from Excel, any rows or columns added will be included in the import phase in PQ

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Anonymous · ‎05-18-2021

Thanks, Paul.

This is a file that I will upload weekly or daily. I don't want to be touching it everytime I want to refresh. Is there another way?

PaulDBrown · ‎05-18-2021

How is the Excel file created? Would saving it as a csv file be an option?

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Anonymous · ‎05-18-2021

Saving as a CSV is not an option because I have 6 months worth of daily files that exist in .xls format and they would all need to be converted.

PaulDBrown · ‎05-18-2021

Or you can create a new query to import the new files (saved to a different folder) and then append them in Power Query, no?

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Anonymous · ‎05-18-2021

I'm a newbie, so I don't know how to do this, let alone the easiest way...

PaulDBrown · ‎05-18-2021

I take it you know how to create a new query to import files from a folder, right? (or how are you importing the data to PBI at the moment?)
If so:

Go into Power Query and:
1) make sure that both imported tables have the same names for the (old) columns.

2) Select the old table, select "Combine" under the Home ribbon, and then select the option "Append Queries"/ Append into new query

3) Select the new table from the "Second table" dropdwon box

4) load and give the new table a new name

5) optionally, you can right-click on both the original tables and disable "load" from the options. The tables will not then be loaded into the model.