Forum Discussion
Combining files from sharepoint with slightly different layouts
- Anonymous9 months ago
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
Hey,
You probably have a transform function and a example file. When using the combine function, If you choose the file with the extra columns as the example file instead of the first file in the combine dialog screen. It might bring in all the columns.
- Anonymous9 months agoNot applicable
That was my thought as well,
it brings over the columns, but there is no data in any part of the column,
unless i try to open filter.
When i open filtering on the new column, it shows only "All" and "(null)". But it also suggests to refresh, because it might not show all, and when i do, suddenly i can filter based on real values in the columns and when i do, suddenly the values appear like there were always.
I have no idea what that is supposed to mean