Get data from multiple files on SharePoint folder

[Mahadevaraobc]

Hi,
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.

[mwimberger]

Hi @Mahadevaraobc 

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

[venal · Accepted solution]

@Mahadevaraobc 

Please refer the link.

Refer one more link for reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more

If this post was helpful may I ask you to mark it as solution and give it some kudos?