Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 months ago
Solved

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 perf...
  • Anonymous's avatar
    Anonymous
    9 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 using 

    List.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