Combining Files Error in Power Query

[Anonymous]

Hi, just starting my Power BI and Power Query journey. I'm trying to 'Get Data' in Power BI from a OneDrive for Business Folder and then combine the files before loading into Power BI. The files in question are xlsx files.

After navigating to the folder via 'Get Data' from 'Sharepoint Folder' and loading the URL in to Power Query, I click the two arrows in the Content column to combine the files. It then returns the following message:

We didn't recognize the format of your first file (). Please filter the list of files so it contains only supported types (Text, CSV, Excel workbooks, etc.) and try again.

I'm lost, the files are excel files. I've tried this with csv files and it works, so....

1. Am I doing something wrong? If so please could someone point me in the right direction.

2. Is this an known issue/bug?

Many thanks.

[wynhopkins]

Just to flag this is actually a bug it should work and it has been raised with the Power Query team.

[PLKRLAS1]

Hi, Just to informt that it is still not working.

Regards,

Krzysztof

[Lukas_tov]

Since the february update of power BI it's working for me.

[wynhopkins]

Yep a fix rolled out in the updated February release last week.

[Anupa_Jayakody]

I get the below error when i combine and load files from a one drive folder to Power BI, can anyone help??

An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=1ST
Kind=Sheet
Table=[Table]

[BA_Pete]

Hi @Anupa_Jayakody ,

Can you post this as a new topic in the Power Query forum please? Make sure to include as much information as possible, such as the file types you're trying to combine and at what stage in the process you are getting the error etc.

This will ensure you get help as quickly as possible.

Thanks,

Pete

[Anonymous · Accepted solution]

Thanks Pete for your help. The ultimate issue was lack of knowledge/understanding of Power Query on my part. 

I found the answer here:

Consolidate Multiple Excel Worksheets using Power Query Custom Functions - YouTube

and here:

Change Power Query Folder Connector when Moving to a SharePoint Folder - YouTube

Both are excellent for those like me who are just learning. Thanks Access Analytics

[wynhopkins]

You're welcome 😊

I have recently hit the same warning when trying to use SharePoint.Contents.  I'm trying to get to the bottom of why this has started happening

[Lukas_tov]

Hi,

I'm having the same issue since the december update form Power BI.

The import from sharepoint folder with excel files is no longer possible. If the files are .csv it works finde. Has anyone a solution to this?

Thanks for any help

[wynhopkins]

Microsoft are in the process of fixing this bug.  No ETA yet.

[PVO3]

@wynhopkins thanks for your clarification

Do you perhapse have a link to monitor this process? I would like to keep updated.

[wynhopkins]

Apparently a fix is available in the latest update to the Feb release

[PVO3]

Thanks wynhopkins!

This indeed seems to be fixed in 2.114.864.0 64-bit (februari 2023)

[Anonymous]

The files have always been .xlsx files. I can 'Get Data' and combine them via Power Query from the local copy of my OneDrive, and indeed I have to create an app.  But as I'm sure you know, to create an app in the service that is refreshed when data is added to a folder you need the 'cloud' file path. 

If i click on the word 'binary' I see the contents of the Csv file. If I do it for an excel file I just see my URL to my OneDrive (XXXXXXXX-my-sharepoint.com) and a file size.

Sorry if the above is not much help.

[BA_Pete]

I'm struggling to recreate the issue at my end. When I go through the SharePoint Folder Connector process, I get the following auto-generated query steps in the output query:

When you get to the stage you describe: "I just see my URL to my OneDrive (XXXXXXXX-my-sharepoint.com) and a file size", have you tried just double-clicking on the icon that you see?

This should import the Excel workbook as a table row, with the embedded table in the [Data] column.

Pete

[Anonymous]

@BA_Pete 

thanks for your reply. However I can't see a reference to hiddern files in the query steps list (although I might not be looking in the right place/hard enough). The folder just has .xlsx file in it, folders with just csv files combine and work. Its only with .xlsx files.

If there is a hidden file in there then it's hidden when I browser to the folder via my local copy of OneDrive or via the online version.

Grateful for any further help. thanks

[BA_Pete]

Did you by any chance take the working .csv queries and copy/paste them to create the .xlsx version?

If this is what you've done, you'll need to update all of the file/folder references in every helper query to be pointing to the correct files. For example, if your parameter query is still pointing at a .csv sample file, the whole process will fail. Either that, or complete the correct process from scratch pointing to the .xlsx folder.

Pete

[BA_Pete]

Hi @Anonymous ,

It sounds as though you have other files in your SharePoint folder that aren't XLSX files.

In the query step list of your output query you should see a step that referece hidden files. Select this step and you should get a table that shows all the files in the folder that are to be processed by the Sample File Function. You can filter this table just like any other ( [Extension] = ".xlsx" etc. ) to ensure you are only including the files from your folder that are relevant.

Pete