Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mahadevaraobc
Helper II
Helper II

Get data from multiple files on SharePoint folder

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.
1 ACCEPTED SOLUTION
venal
Memorable Member
Memorable Member

@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?

View solution in original post

2 REPLIES 2
mwimberger
Resolver II
Resolver II

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
Memorable Member
Memorable Member

@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.