combined files do not show all column values

[KH24]

Hi,

I tried to find an answer but could not. So here is my question.

I have 4 Excel files that I load from Sharepoint into my PBI file via Transform and then combine into 1 query. Now these files show Business Lines shares on projects. Therefore 90% of columns are identical but 2 files have a handful of extra columns (including that the Business Line name changed over the last 2 years so I have 2 columns for one Business Line).

These extra columns do not show up when I click on "combine" where it shows the sample file and therefore they do not show up in the Query Editor either.

Now I tried to combine them into one table. I found a solution where it was suggested to go into the Advanced editor (before any Changed Type step) and add an empty table with all possible column names and then combine those tables. Here is what I added (in red) :

#"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Column3", "GPR Region", "GPR Subregion", "Column22"}),

ColumnNamesTable = #table ({"Source.Name", "Partner Code", "", "OrderNumber", "BL ST", "BL Diff","BL CI", "BL PT", "BL RO", "BL D&A", "BL SY", "Overall Result"},{}),

CombineTables = Table.Combine({#"Removed Columns", ColumnNamesTable}),

#"Changed Type" = Table.TransformColumnTypes(CombineTables,{{"Source.Name", type text}, {"Fiscal year/period", type text}, {"Partner Code", type text}, {"", type text}, {"OrderNumber", type text}, 

etc etc

At first it looked like it worked perfectly. All columns show up.

But then I realized those added columns have no values at all! Any idea what I can do differently?

So for example my "example" file (in the combine window) says:

Order Numer	Business Line A	Business Line B	Business Line C	Overall Value
999	100	50	50	200

The other file goes something like this:

Order Numer	Business Line A	Business Line D	Business Line E	Overall Value
777	50	100	200	350

In the end I need one table that shows

Order Numer	Business Line A	Business Line B	Business Line C	Business Line D	Business Line E	Overall Value
777	50			100	200	350
999	100	50	50			200

At the moment my result looks like this:

Order Numer	Business Line A	Business Line B	Business Line C	Overall Value	Business Line D	Business Line E
777	50			350
999	100	50	50	200

I don't care that Business Line D and E are showing behind the Overall Value, but I need to see their values.

Any idea what I need to do?

Thanks

[Anonymous · Accepted solution]

Hi, @KH24 

When I reviewed the posts in our community, I found that no one had replied to this post yet. After reading it, I have the following reference ideas:

You need to make sure that the column headers are consistent between the files. Before merging files, make sure that all files have a consistent set of column headers, including placeholders for missing columns in some files. You can do this manually in Excel before you load a file into Power BI or use Power Query to programmatically add missing columns.

Use Power Query's Table.Combine function: Instead of adding an empty table with column names, use the function in Power Query directly to merge tables. Power Query should automatically align columns with the same name and populate missing columns with null values if the data is not present. Here's a simplified approach

let
Source = SharePoint.Contents("YourSharePointSiteURL", [ApiVersion = 15]),
CombinedTables = Table.Combine({Table1, Table2, Table3, Table4})
in
CombinedTables

After you merge the tables, you may need to manually adjust the resulting tables to ensure that all columns are properly aligned and that the data type is set correctly for each column.

Make sure that steps in Power Query, especially any transformations or column removals, don't inadvertently remove or alter data in extra columns.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KH24]

Thanks @Anonymous That was a great tip and it works with the CombinedTables solution!

To adjust it in Excel was not really a great option as I have a file for each year and would need to go back into every old file whenever a new year has some new columns. The only thing I need to think of now is that I have to add my new Year's file into the "CombinedTables" function and test it, but that should be easy enough.