Datflows are not supporting .xlsm file format for combining query

[Anonymous]

Dear Team,

I have to connect to a sharepoint folder to combine multiple excel files. File extension of all the files in the folder  is .xlsm.

When i use Data flow to connect to this folder and combine all the .xlsm files it throws an error as below in th esnapshot.

I am able to combine these files with the help of the Power BI desktop as well as with the help of the Power Query in Excel.

Kindly help me to solve this issue.

Any workaround to solve this issue would also be welcome.

Best Regards,

Divesh Goyal

[v-robertq-msft]

Hi, 

According to my research, here are some related cases, you can take a look and check if they are useful:

https://community.powerbi.com/t5/Power-Query/open-file-as-text/td-p/683214

https://community.powerbi.com/t5/Desktop/Error-combining-files-in-Power-query-from-Azure-Datalake-de...

https://community.powerbi.com/t5/Desktop/Unzip-and-load-csv-from-web-error-quot-we-didn-t-recognize-...

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

[Anonymous]

Hi,

none of the suggested solutions are appropriate for my situation.

I am reading a sharepoint folder where i have kept multiple .xlsm files which needs to be combined into a single query for further processing. I am able to do the same via Power BI Desktop as well as via the excel query. But when i am using the dataflows to combine these files i am getting these errors:

1. Complex Types: Some complex types can not be loaded would you like to remove these columns in the query?

2. If i ignore and combine the binaries in the content folder, ia m getting the error message as follows: "We didn't recognize the format of your first file. Please filter the list of files so it contains only the supported files (Text, CSV, Excel Workbook, etc.)

[MS_pBiAtRB]

Hi,

I got the same problem while trying to combine .xlsm files from a SharePoint online data source.

@Anonymous did you figure out any solution by now?

Kind regards,

Moritz

[Anonymous]

Still Looking for solution from Power BI developers, no response from them. Its a very immature behaviour of Dataflows that they do not consider .xlsm as an Excel file.

atleast from the functionality point ETL process should be consistent across Power BI, Excel and Data Flows.

As a WA, i am using a flow to copy those files in a different folder at periodic intervals and changing the extension to .xlsx :). That works. You can search Google for creating such a FLOW.

Regards,

Divesh Goyal