Trying to combine Multiple Excel files from SharePoint on PowerBI

[mkrapez]

I followed the tutorial from https://powerbi.microsoft.com/fr-fr/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

The excel files have 4 rows empty before the headers 

- So I tried two approaches:

Not combining the files yet and just editing them to filter only the files that I want that have the same headings. Then combining them and it only gives me one files rows and all others are null. Also, when I click the filter button on the header it says List may be incomplete, and click Load more, I load more and it shows all the feilds that I wanted on the filter tab but dont show on the acutal spreadsheet.

 - Also tired what was recommended which was to combine them, all the excel files have 4 rows empty before the headers not sure if this causes a problem. and when I click okay it only gives me the rows of the example file I picked, and all other values are Null.

Not sure why its not combining them even when I have all the files filtered to the correct ones, also the button that I see everywhere that says 'Combine Binaries' on mine its called Combine Files? Please help me figure this out. 

[mkrapez]

Okay, so I was able to combine them all correctly but I do not have the data source yet! I've tried the same process on Excel. The problem is when I try to apply the query it takes probably 2 hours to try and load all the rows, and it seems to load them twice. then I get an error saying im not connected anymore and that my credentials are invalid but they arent. I am just trying to combine them all and be able to use them. But they are just stuck in power query ?

[v-chuncz-msft · Accepted solution]

@mkrapez,

You may check the steps in The Advanced Editor.

[mkrapez]

I followed the tutorial from https://powerbi.microsoft.com/fr-fr/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

The excel files have 4 rows empty before the headers 

- So I tried two approaches:

Not combining the files yet and just editing them to filter only the files that I want that have the same headings. Then combining them and it only gives me one files rows and all others are null. Also, when I click the filter button on the header it says List may be incomplete, and click Load more, I load more and it shows all the feilds that I wanted on the filter tab but dont show on the acutal spreadsheet.

 - Also tired what was recommended which was to combine them, all the excel files have 4 rows empty before the headers not sure if this causes a problem. and when I click okay it only gives me the rows of the example file I picked, and all other values are Null.

Not sure why its not combining them even when I have all the files filtered to the correct ones, also the button that I see everywhere that says 'Combine Binaries' on mine its called Combine Files? Please help me figure this out.