Forum Discussion
Get data from multiple files on SharePoint folder
I have a SharePoint folder which has multiple Excel files and each files has 3 worksheets and only 1 sheet has rawdata. Each rawdata is about 1 million records.
I would like to know how to combine all the rawdata to 1 file which I can use to visualize in pbi.
2 Replies
great reply from venal . What I have found in my experience is the combine binaries ( and the associated custom function / helper queries ) can slow the upload down quite a bit. And it makes it quite messy if you have many of these loads to do with a big ETL.
So I went looking for a better method a while back and I found one from Ruth at Curbal.
Curbal Mutiple Files from Folder
let
Source = SharePoint.Files("https://XXXXXXX", [ApiVersion = 15]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook ([Content])),in
#"Added Custom"The gist is to adjust the M code to something like this: ( Where XXXX is your sharepoint location)
This allows for a faster and cleaner load of the files ( when there are a whole bunch)
Hope this helps with the solution down the track.
Manfred
