topic Consume Excel file stored in M-File from PowerBI in Report Server

Consume Excel file stored in M-File from PowerBI

GiulianoB — Thu, 01 Feb 2024 09:06:02 GMT

Hello,
Is there anyone who has configured PowerBI or a Dataflow in a way to consume an Excel file directly from an M-File Vault?

By using the "Get Data" from PowerBI, I'm able to find the file from the M-File desktop version I'm using but then, a local copy of it is getting created in my SharePoint personal folder. Unfortunately in this way, the file used by PowerBI is not any more connected with the real source.

I've found on the network the suggestion to work in the opposite way around. Something like:

- Store the Excel working file in SharePoint

- Link PowerBI to SharePoint (very easy)

- Copy the file to M-File when approval is needed

... but I need to secure that my PowerBi is using only the latest approved data from the source.

Is there any official best practice on this matter that I can use?

Thanks!!!

Re: Consume Excel file stored in M-File from PowerBI

Anonymous — Fri, 02 Feb 2024 02:06:20 GMT

Hi GiulianoB ,

It works according to the method you described, but you need to make sure that the source is up to date.

The following M-languages are available:

let varName = Text.BetweenDelimiters([File Name], "_", ".") in #datetime(Number.From(Text.Start(varName, 4)), Number.From(Text.Middle(varName,5,1)), Number.From(Text.Middle(varName, 6, 2)), Number.From(Text.Middle(varName,8,2)), Number.From(Text.End(varName,2)),0)

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Consume Excel file stored in M-File from PowerBI

JOOV2 — Wed, 01 Oct 2025 12:25:40 GMT

Hi GiulianoB,

An update on this from my side in the hope it helps others...

We currently have two solutions:

1) Connect with the file via the local M-files M:/-drive and use a personal on-premises gateway for scheduled refresh.

2) Create a Power BI Custom connector for connecting to files in M-files, including authentificatio logic. And install an on-premises gateway to connect to the custom connector .MEZ file.

Best regards!