Forum Discussion
Add Excel Data Source with Additional Columns
You can see the M code in the Advanced Editor in Power Query (right click on any query, click "Advanced Editor". If you used the official merge option there will be a new section with a ingestion function and a sample file reference. Walk through the generated code, and find the place where it mentions the sample file (for the headers). Usually it is the first file in your list.
That may actually give you an in. When you list the items in the sharepoint folder, sort them in a way that newer files come first. Then the merge code will pick up the new structure (and gracefully merge the older files even if they are missing the new column).
You can see the M code in the Advanced Editor in Power Query (right click on any query, click "Advanced Editor". If you used the official merge option there will be a new section with a ingestion function and a sample file reference. Walk through the generated code, and find the place where it mentions the sample file (for the headers). Usually it is the first file in your list.
That may actually give you an in. When you list the items in the sharepoint folder, sort them in a way that newer files come first. Then the merge code will pick up the new structure (and gracefully merge the older files even if they are missing the new column).
Thank you lbendlin , while it wasn't as graceful as I had hoped, your suggestion led me to figure out a solution. I'm still VERY new to Power BI, so it's possible that my initial configuration is to blame. However, I used the following steps to incorporate your suggestion.
- Duplicated the Power BI report, in which all the Applied Steps in the Power Query were removed except the Source and Filtered Rows (steps 1 and 2). Then I ran the Combine Files process to use the new Excel file as the Sample File.
- Returned to the original Power BI report and added the new Excel file to the Filtered Rows step.
- Updated the Helper Query to use the same Sample File logic as the duplicated report
- Power Query automatically ran, applying the new Excel file as the Sample File. This added all the new columns to the final query while not corrupting the subsequent steps of the Power Query.
Thank you lbendlin and Jimmy801 for your insight and support!
