Forum Discussion
Combining files from sharepoint with slightly different layouts
Hello,
I am working on ticket reporting tool, using Power BI abillity to connect to sharepoint folder.
Lately when I was working with multiple similar-layout excel files, the connection works perfectly. However when I try to add new file with, lets say, one extra column, the shanenigans begin.
For combining files I use 'combine' in power query. It requires me to select a sample file and then it handles the rest. When I select the file with new column as a sample, the result query returns table WITH the column, but ALL values are set to 'null'. This seems to occur even when I try to combine files more dynamically with getting list of all columns from all files first.
This is something I feel should be working, but this is not the crazy part.
When I apply ANY filter or even try to sort the values in the power query, the values suddenly appear!??
Edit: and when I try to filter out duplicates after, the data dissapears again
What is this microsoft magic, i am baffled. Is there anyone with suggestions on how to approach this better or differently?
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
10 Replies
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
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.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
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
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 extraNumber 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?
Link to relevant thread
Solved: Combining files with different field names from Sh... - Microsoft Fabric CommunityI'm glad you found a solution! We're here in the community to support each other.
Regards,
Rufyda Rahma | MIE
