PowerQuery | Sharepoint & Excel with Multiple Tabs

[ovetteabejuela]

First time attempt but I used to do this on a local directory and is working fine using File.Contents however this technique doesn't seem to work in Sharepoint because it's asking for a "valid absolute path" where "https://" is not.

So I gues my question is, given the scenario:

1. Your data source is Excel and it has multiple tabs, all tabs identical in columns/fields

2. These files are in SharePoint/OneDrive, actually in my case OneDrive of another person.

How do you import these in PowerQuery?

I don't have an issue importing from Sharepoint or OneDrive folder, the challenge is how to import these multiple tabs.

[v-cherch-msft]

Hi @ovetteabejuela

Below are some similar posts for you to check if they could help you.

https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

https://community.powerbi.com/t5/Integrations-with-Files-and/Import-CSV-Files-Then-Total-Them/td-p/5...

https://community.powerbi.com/t5/Integrations-with-Files-and/Need-to-load-all-Excel-files-in-a-given...

Regards,

Cherie

[ovetteabejuela]

Hi @v-cherch-msft,

Thanks but I think what this shows is how to load stuff from OneDrive which I really don;t have a problem with. The problem that I'm facing is:

How to load(merge) multiple tabs from an excel file while it is on one drive, the thing is I am able to do this given that the file is on a local drive. I was mainly using a function called "File.Contents" where it is only accepting absolute path, now that the files are in OneDrive I don't have the absolute path anymore and thus is an invalid parameter for File.Contents() function. I can't sync the OneDrive library -it will defeat the purpose of placing them in OneDrive.

So again, how would someone load/merge multiple tabs from multiple excel files given that they are in OneDrive? I hope someone could show me, tried researching on this but nothing relevant pops up.