cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
redridgie
Regular Visitor

The Day I Been Dreading

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,

 

7 REPLIES 7
PaulDBrown
Super User II
Super User II

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.






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?

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.






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. 

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.






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

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.

 

Appending tables.gif

 





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.






Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors