Forum Discussion
Combining files from sharepoint with slightly different layouts
Thank you,
You are right, the problem was with Power Query not reaching all data by default. However it seemed to be caused even by large amount of data rows in each file. Power query doesn't access all data for preview as it would be too slow and it shows only i think 1000 rows. If those rows are taken from single file, which is missing the column, even when all columns are defined dynamically and they do show in preview, the filter "sees" only null values, because none other are in the file.
When forced to refresh, or as it is in PQ: "Load more" in the filter, PQ is forced to go through all files to show all possible values. After this it just happens to be enough to select OK and it applies a filter, that takes all rows into concideration and no data seems to be missing.
So the solution that worked for me was usingList.Distinct(List.Combine(List.Transform (#"Removed Other Columns1"[Transform File],each Table.ColumnNames(_))))
in
= Table.ExpandTableColumn function
for dynamically creating all columns from all files.
This one worked by itself for small files with even totally different structure.
The extension for making sure all data is included is by opening filter on one of the columns in Power Query and pushing "Load more" if the program allows you to.
This creates extra step "filter" that looks like this
= Table.SelectRows(#"Expanded Table Column1", each true)
In combination this solved my issue.
Thank you all for advices
Hi Anonymous ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you PhilipTreacy Chewdata for the prompt response.
The issue isn’t with DAX or filtering - it happens because the older files don’t contain the new Country column. When Power BI combines the files, the sample transformation ignores that column, so the result shows all nulls, and the values only appear when you force a refresh (like opening a filter).
In order to fix it, follow the below steps:
In Power Query, edit the Transform Sample File step so that:
You define all expected columns, including Country.Any missing columns are added as null.Columns are aligned in the same order.This makes every file share the same structure, so the combined data will show real Country values for new files and nulls for old ones - without disappearing during filters.
Thank you,
You are right, the problem was with Power Query not reaching all data by default. However it seemed to be caused even by large amount of data rows in each file. Power query doesn't access all data for preview as it would be too slow and it shows only i think 1000 rows. If those rows are taken from single file, which is missing the column, even when all columns are defined dynamically and they do show in preview, the filter "sees" only null values, because none other are in the file.
When forced to refresh, or as it is in PQ: "Load more" in the filter, PQ is forced to go through all files to show all possible values. After this it just happens to be enough to select OK and it applies a filter, that takes all rows into concideration and no data seems to be missing.
So the solution that worked for me was usingList.Distinct(List.Combine(List.Transform (#"Removed Other Columns1"[Transform File],each Table.ColumnNames(_))))
in
= Table.ExpandTableColumn function
for dynamically creating all columns from all files.
This one worked by itself for small files with even totally different structure.
The extension for making sure all data is included is by opening filter on one of the columns in Power Query and pushing "Load more" if the program allows you to.
This creates extra step "filter" that looks like this
= Table.SelectRows(#"Expanded Table Column1", each true)
In combination this solved my issue.
Thank you all for advices