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
I cannot provide you with the eact files,
would it be sufficient if present the excel file layouts here?
In sharepoint folder where the data is saved are files with following columns:
| Number | Affected User | Short description | State | Category | Priority | Assignment group | Updated | Assigned to | Closed | Opened | Opened by Group | Channel | Country code |
The data i am trying to include have simillar structure, but with one column extra
| Number | Affected User | Short description | State | Category | Priority | Assignment group | Updated | Assigned to | Closed | Opened | Opened by Group | Channel | Country code | Country |
Or did you mean the DAX formulas from power bi?