Add Excel Data Source with Additional Columns

[jsuttmann]

In Power Query, I'm using a SharePoint document library as my data source with multiple Excel files, all of which have the same columns, so Power Query automatically merged the data from these Excel files into a single table.  All good.  Now, I've added a new Excel file to this document library and included it in the Power Query.  However this new Excel file has additional columns that are not present in the other Excel files.  However, upon refreshing my data, the data set only pulls in the columns from the new Excel file that match those in the other Excel files and ignores the new columns.  Since I never specified which columns to include in the transform, shouldn't all the new columns be included as well?  How can I force Power Query to include these new columns?

[Jimmy801]

Hello @jsuttmann 

the bottleneck here is the ExpandTableColumn-function that hardcoded the column from your first file (sample file). To overcome this you have to write this part of your code dynamically. So instead of gettting the columnnames of the first file

(the code that look like this Table.ColumnNames(TransformFile(SampleFile)) )

you have to change it to something dynamically like this

Table.ColumnNames(Table.Combine(PreviousStep[ColumnNameOfExtractedFiles]))

without seeing your code I cannot give you the exact to code to put

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lbendlin · Accepted solution]

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).

[jsuttmann]

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.  

1. 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.
2. Returned to the original Power BI report and added the new Excel file to the Filtered Rows step.  
3. Updated the Helper Query to use the same Sample File logic as the duplicated report
4. 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!

[jsuttmann]

Hi @lbendlin, I'm pulling data from named tables.  The table name is the same in all the Excel files.  I do need the new columns, so I can't ignore them.

I'm not sure how to see the merge code.  My applied steps are:

1. Identify the source:  a SharePoint site address

2. Filtered Rows to pick the Excel files to be included in the merged query (including the new Excel file)

After this step, I would typically use the "Combine Files" function in the Content column.  However, if I do that now, I believe it will corrupt the transform steps added after #2.  By including the new file in #2, I am able to pull in the new Excel file, but not the new columns.  

[lbendlin]

Are you pulling data from Excel sheets, tables, or ranges?

Check your merge code, it uses "the first"  file by default as a template ("Sample File") to get the list of headers and ingest all the other files. You can change the Sample file pointer to point to your new file, or you can rewrite the Power Query to not use sample files at all.

Do you  actually need that new column?