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
Anonymous
The idea when you load multiple files, and apply the same transformations, is that those files all have the same structure.
If you have files with different structures, you would normally have different queries to load and transform them.
If you have been loading files with different structures and transforming them with the same query, I'd say you've been fortunate that that has worked.
As for your current situation, hard to say exactly what is going on. I'd need to see your queries and some examples of the files.
Regards
Phil
I uderstand the idea. However I need to find a way how to combine old data files with new data files, that have some columns extra. And I am unable to find a way how to do it. The tool I am trying to provide should read all data files from a folder specified and create visuals based on it.
This happening suggests that Power BI is able to show and access the extra columns values, however refuses to, probbably for the reason you are mentioning.Anonymous
I'd need examples of those files to be able to build any solution for you.
Regards
Phil