Sharepoint Data Import - Multiple File Processing

[Las]

Hi Experts,

I'm planning to import data from Sharepoint Folder and later on adding new delta files with same format and enable schedule refresh. During Initial file when loading i created a new query for each table that I want to extract but not sure during the refresh process these tables will get updated with new data? Also when combining PBI creates helper queries, why do we need this? or is there a better way of handling sharepoint files. 

Please advise the best practice on loading and processing multiple files with schedule refresh.

Thank you.

Las

[BA_Pete]

Hi @Las ,

The best way to handle multiple files of the same format, if you want them to all be combined into a single table, is to use the SharePoint Folder connector and use the "Combine & Transform" button, which it sounds as though you are doing already.

Not sure what you mean by "i created a new query for each table". Using the folder connector should allow you to bring in all of your files at once, then combine them into just one table.

Regarding the helper queries, let's go through them one-by-one and explain what each is doing (your query names may not appear exactly as per my screenshot, but they should be fairly easily identifiable):

Okay, so top to bottom:

sampleFileParameter = This tells the query which file in the SharePoint folder to use as an 'example' of what all the other files in the folder are like. Assuming that all files in your folder are the same type of data, and the same format/structure, then the actual file you use should be irrelevant. This will be the file that you perform the trasformations on that you want to be applied to EVERY file in the folder before combining them.

sampleFile = The actual file binary as identified by the parameter above.

xFormFileFromVehicleActivity = This is the dynamic function that is built from your example transformations performed on your sample file. This function will be applied to all other files in your folder in order to replicate the transformations you did on your sample file across all the other files.

xFormSampleFileFromVehicleActivity = This is the query in which you perform your desired transformations on your sample file. Every transformation you perform in this query will be translated into the dynamic function above and thus performed on every other file in the folder before they are all combined into a single table.

gps_14 = This is the result query containing every file in your folder appended together, having had every transformation step that you applied to the sample file applied to each.

For the most part, the only helper queries you should/would make changes to are:

xFormSampleFileFromVehicleActivity - to adjust the transformations applied to every file

gps_14 - to add further transformations to the combined dataset

More detail from MS Docs:

https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder 

Pete

[Las]

Hi @BA_Pete ,

Thank you for your detailed response and really appreciate that.

When creating a connection to Sharepoint Folder to extract a file / folder it always create a helper query for each query. How should i better manage these queries? Is there a way to use one helper query for all the files? So that its neat and clean in the power query pane.

Thank you.
Las

[BA_Pete · Accepted solution]

Hi @Las ,

It depends on whether all the files that you are getting from the different folders are the same structure and data type as one another.

If you're keeping different types of files in different folders, then you will need a set of helper queries for each different folder, as the transformations that will need to be performed on the files in each folder will be different.

If the files in all folders are all the same structure/data type, then I would recommend throwing them all into one folder so you can manage the whole lot with a single set of helper queries. If you want to keep the folder structure locally for eas of reference, you may be able to use Power Automate to automatically copy new entries into a combined folder for you.

If you try to put files of different structure and data type into one folder and run them all through the Combine & Transform process, you will get errors or, at the very least, unexpected and/or unreliable output.

Pete

[Las]

Thanks @BA_Pete  that explains clearly. Much appreciate your help. Have a good one!