Loading SharePoint folder files without combining - efficient way

[ronaldbalza2023]

Hi everyone, how can load files in sharepoint folder without combining them? Every file has its own table/columns/content and all of them reside in the sharepoint folder. Im asking this as I think there should be a more efficient way to load tables to my data model rather than creating a new connection for each one of the files. Is this even possible? Copying creates an enormous transformation file. Thanks in advance 🙂

[danextian]

Hi @ronaldbalza2023 ,

You can have a single main query that is connected to a Sharepoint Folder and reference to that query to that connect to each file. This main query doesn't need to be loaded to the data model.

Query: SharepointMain

let
    Source = SharePoint.Files(SharepointSite, [ApiVersion = 15])
in
    Source 

ReferencingQuery:

let
    Source = SharePointMain,
    #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "MyFile.xlsx"),
   
in
    Source 

[ronaldbalza2023]

Hi @danextian thanks for taking the time on this. However, nothing really happen.

[danextian]

Hi @ronaldbalza2023 ,

What do you mean by nothing? If you wan to connect to a file, you can just click on Binary right before the filename. Power BI then will try to figure out what the file type is and use the necessary data source function (Csv.Document, Excel.Workbook, etc).