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?
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
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?
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.
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?)
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.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates