Creating a dataflow to a macro-enabled excel sheet in a SharePoint Folder

[michaelyoung1]

Hi,

I'm trying to connect to a .xlsm document in a Sharepoint folder using Power BI online. I've used Power BI desktop and connected to local versions of this document and it's worked completely fine, however when trying to set up this dataflow I connect to the folder fine, find my file and click "binary" and it doesn't seem to be able to open the file as it has done with all .xlsx I've worked with.

Is this feature supported? Or is there an alternate way to connect to macro-enabled documents stored in SharePoint/OneDrive, so that multiple people can still access them and Power BI will be able to do it's thing automatedly.

Thanks,

Michael 

[Fowmy]

@michaelyoung1 

Can you check this link: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

[michaelyoung1]

@Fowmy 

Thanks for your reply. I've already tried this having seen it in a question elsewhere.

For some reason when I do this method, after Step 3 I get this error message 

Occasionally it makes it through to step 4 but I have to close and reopen Power BI, and I get this error anyway after clicking "connect" here. This is despite being an Owner of said SharePoint, and being able to connect to other folders in the same SharePoint completely fine using dataflows in Power BI online.

[v-easonf-msft · Accepted solution]

Hi, @michaelyoung1 

I'm afraid it couldn't achieve in Power BI for now. The online connector can't  get contents of xlsm files. You may need to convert your file to .xlsx first.

You can also open a support ticket with MSFT to seek technical support , usually they will have better suggestions.

Related idea you can vote for :

Data flow with xlsm files 

If there are enough votes, the Product Team will evaluate and take good consideration about it.

Best Regards,
Community Support Team _ Eason

[michaelyoung1]

Thanks @v-easonf-msft 

Strangely I have figured out that it is in fact possible, going into the advanced editor for an xlsx document and changing the path to my xlsm worked completely fine!

Fingers crossed it doesn't break now.

Thanks,

Michael