Forum Discussion
combined files do not show all column values
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 CombinedTablesAfter 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.
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
CombinedTablesAfter 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.
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.