From SQL to PowerBI : intermediary file

[LogiqueNeutre]

Good morning community,

after a lot of work, i have to admit i fail at solving this problem.

I currently have several Power BI files, each using a connector, and each being refreshed twice a day by this connector. They get most of their data from a single SQL database, which also has the connector.

I thought i could reduce a lot the server load induced by the refreshes by having an intermediary file, doing the requests (twice a day, still), and then having each Power BI file getting their data from this intermediary file instead of the SQL database.

If i make this file a Power BI file, i can't find out how to properly get data to other Power BI files from this Power BI file.

If i make this file an Excel file, i tried many things, but i don't find how to properly get it updated twice a day without having a dirty solution like the server opening this Excel and a macro refreshing it (which will not happen). I found a "solution" in power automate, but it doesn't work (the script gets executed, supposedly, the data doesn't get refreshed, while power automate says the flow worked fine.

I also found this, https://damobird365.com/export-power-bi-to-excel-with-power-automate/#Populate-an-Excel-file-with-Gr... , but i don't fully understand everything involved, which makes it a nightmare to reproduce, and even more to maintain, if it really is a solution, which i'm not certain of.

I also tried and considered other things, but all failed.

Thank you for any help, any lead, any info !

[v-mdharahman]

Hi @LogiqueNeutre

[LogiqueNeutre]

Hello. 

This problem is still an issue for my company. 
However, i will no longer be part of the company soon. I explained all the details of this problem to another employee, who is logged here as Coder_BI, and he will, when he has time to dedicate to this problem, come here for help and news about the issue.

In the meanwhile, since the problems are unchanged, do not hesitate to post more new working solutions if you think about some.

Thank you for your help, and i hope you will be nice with him ! 🙂

[LogiqueNeutre]

You have all my apologies for the delay. This topic is still an issue to my company.

I'll be back to it with what you asked as soon as possible. In the meanwhile, the issue might become easier to solve, since the company is considering using a Premium Per User account instead of a Pro one.

[v-mdharahman]

Hi @LogiqueNeutre,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Best Regards,

Hammad.

[v-mdharahman]

Hi @LogiqueNeutre,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[LogiqueNeutre]

Hello,

i've tried what was told me, and once it did the same problem i reported, once it didn't. Since i wasn't using at that time a file that can properly test the solution, i'm first finishing the job so that i can ACTUALLY test the solution in real conditions and then tell you if it works or not, and in this last case, show a detailed report with screenshots of what's happening.

In a few days, you should see another more on-subject answer from me.

[v-mdharahman]

Hi @LogiqueNeutre,

If you are still unable to solve the issue you can reach out to Microsoft Support by raising a ticket with Microsoft Support.

Please refer below link on how to raise a contact support or support ticket.
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Best Regards,

Hammad.

[LogiqueNeutre]

When i use "Get data", "Semantic model", and want to use the semantic model i published on the newly created workspace, it tries to do a direct query, then fails saying

DirectQuery à AS – [nameOfTheModel]

AnalysisServices : L'utilisateur '[email of my user]' n'a pas l'autorisation d'appeler la méthode Discover. Détails techniques : RootActivityId : [some id] Date (UTC) : 2/20/2025 9:34:21 AM

Also i don't see where the request newly created "DirectQuery à AS – [nameOfTheSemanticModel]" is in Power Query, i searched everywhere. I do see it "opened" but i don't know where from. It has the 3 steps i've written earlier. On the second one, called "Cubes" and being "= Table.Combine(Source[Data])", there is a warning "This step generates a query that isn't supported by DirectQuery." and there's a button to swap all tables to Import mode, which would be good to me, but when i do, it tries to refresh all data, and does the same mistake i've written up there.

In https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-discovery it is said that there's an option to tick to make the dataset discoverable. There isn't : the menu "Endorsement and discovery" is actually a menu "Endorsement" only. I can't find the option anywhere.

If needed i can provide more details on more things i tried to solve this problem one way or another.

[v-mdharahman]

Hi @LogiqueNeutre,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you’re trying to optimize the refresh process of multiple PowerBI reports which are pulling data from a single SQL database. As @nilendraFabric responded to your queries kindly go through his responses and if you are able to solve your issue then please mark the helpful reply as solution.

I would also take a moment to thank @nilendraFabric, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[LogiqueNeutre]

I'm probably doing something wrong, i cannot connect the end Power BI files to the intermediary Power BI file.

Could you please provide a detailed step by step way to do it ? My online researches have so far not helped at all.

[v-mdharahman]

Hi @LogiqueNeutre,

It sounds like you're trying to connect multiple Power BI reports to a central dataset but are running into some issues. Here’s a step-by-step guide to ensure everything works fine
* Make sure the Intermediary Dataset is Published to a Shared Workspace. Go to your PowerBI Desktop

   and connect to your SQL database. Do any necessary transformations and modeling and then publish

   the report to a shared workspace.

* Enable DirectQuery for Live Connection by going to the workspace where you published the dataset in

   PowerBi Service. Now go to Dataset's setting and enable DirectQuery for Live Connections if you want

   real-time updates.

* Now connect other reports to the published dataset by creating a new report. here select the data

   source in Get Data as Power BI Semantic Models. Select the workspace where you published the

   dataset and choose the dataset and click Connect.

Now after your report is done you can publish them as usual. 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[LogiqueNeutre]

Thank you for your message.
Step 1 : "

Make sure the Intermediary Dataset is Published to a Shared Workspace. Go to your PowerBI Desktop

and connect to your SQL database. Do any necessary transformations and modeling and then publish

the report to a shared workspace."
Done.

Step 2 : "

Enable DirectQuery for Live Connection by going to the workspace where you published the dataset in

PowerBi Service. Now go to Dataset's setting and enable DirectQuery for Live Connections if you want

real-time updates."
This is the part i don't find at all. I search everywhere in the newly created workspace, in settings, also elsewhere, and i don't find this setting, DirectQuery for Live Connection.

I searched on the web also, and i find nowhere where can this option be.

[v-mdharahman]

Hi @LogiqueNeutre,

Thank you following up on our previous conversation. Regarding the "Enable DirectQuery for Live Connections" setting this option is only available if your Power BI workspace is in a Premium Capacity (P1 or higher). If your workspace is Pro or Free, this option will not appear in the dataset settings. 

If you don't have Premium capacity, you can still connect your reports to the published dataset using Live Connection, which works similarly to DirectQuery.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[LogiqueNeutre]

I don't know how to say it any other way. So i'll put images.

1. I published my first Power BI dataset, let's call it "Donnees" which means Data in french, to a workspace that is not "My workspace" but another.

2. When i am in the second Power BI, i try to do as you said, "you can still connect your reports to the published dataset using Live Connection". First, i click "Get data" then "Power BI semantic model", look :

 3. then i click this dataset (hidden the dataset sensitive data in red, the other in dark red) and then "connect" :

4. a window appears, "Connection to your data", which shows the various tables existing in Donnees, and i choose which to import and click "send", look :

5. a window appears, showing it is processing the thing :

It works out,

6. Then i see i got new tables in Data, starting by the prefix i set,

7. Then i go to transform data editor, and i cannot find or seem to be able to load any of the tables i've just loaded.

I read online that direct query doesn't enable much transformation, that the data must be worked in the source file.

…ok then, how do i copy my hundreds of power query steps at once from my 96 tables into the source Power BI file ..??

[v-mdharahman]

Hi @LogiqueNeutre,

Thanks for sharing the details. Based on what you’ve described, the issue is that Power BI does not allow modifying Power Query transformations when using a Live Connection to a published dataset. Instead, all transformations need to be done in the source dataset before publishing. This is by design Microsoft doesn’t allow modifying a dataset from another report. To resolve this, you’ll need to move your Power Query transformations into the original dataset. 

You can manually copy PowerQuery steps (M Code) by following these steps:

* Open your current PowerBI file then go to "Transform Data".
* Right-click each table and then click Advanced Editor. Then copy the M code.
* Open the original dataset file and go to Transform Data.
* Click New Query then Blank Query and then paste the copied M code.

* Repeat for all tables, then Close & Apply and Publish again.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[LogiqueNeutre]

Hello,

thank you for your answer and the suggestion.

While i don't fully rule out this idea, i would like to check (with anyone willing to help) that there is no other solution to my problem.

Remember (as explained in my first posts here), my problem is that i don't find how to properly factorize my requests into one file only (whatever the type), and then have my 3 power bi getting their data from this single file.

We have explored here the solution of a single power BI file then sharing its dataset to the 3 next power bi files.

How about an excel file, with 1 table per tab ?

How about power automate ?

(My company doesn't use Azure, so i doubt the Azure Data Lake Storage can be used here)

The drawback of having to separately modify two files for one result only is a bit annoying. If i can avoid it with a better solution, let's find out, it will also avoid me copying 130+ sets of queries (yes, with the advanced editor, which factorizes the queries into one) from 3 Power BI files into 1 Power BI file.

Thank you everyone.

[v-mdharahman]

Hi @LogiqueNeutre,

Thanks for your follow-up. I understand that copying almost 130 queries manually is not an easy task, so let's find some other ways to do it:

As you wanted to try storing data in Excel, let’s check if it can work as a Data Source. Follow these steps to create an ecxel file for your data.

* First Create an Excel file with one table per sheet.

* Then Load data from SQL using Power Query in Excel.

* Now you have to save the file in OneDrive or SharePoint for accessibility.

* In Power BI, connect your reports to this Excel file.

* You have to use Power Automate to refresh the Excel file twice a day.

Another option is using Power Automate for Automated Refresh. Instead of Excel, keep your Donnees dataset and use Power Automate to refresh it twice a day. Your three Power BI reports connected to it via Live Connection.

You can also use Power BI Dataflows stored in OneDrive or SharePoint. First create a Dataflow in Power BI Service to centralize SQL queries then connect all three reports directly to the Dataflow instead of SQL. Using this you don't need to manage multiple copies of queries.

I hope this solves your current requirements. If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[LogiqueNeutre]

I'd love to find out how to properly use Power Automate to refresh the Excel file twice a day. Quoting my own very first post in this conversation,

"If i make this file an Excel file, i tried many things, but i don't find how to properly get it updated twice a day without having a dirty solution like the server opening this Excel and a macro refreshing it (which will not happen). I found a "solution" in power automate, but it doesn't work (the script gets executed, supposedly, the data doesn't get refreshed, while power automate says the flow worked fine.

I also found this, https://damobird365.com/export-power-bi-to-excel-with-power-automate/#Populate-an-Excel-file-with-Gr... , but i don't fully understand everything involved, which makes it a nightmare to reproduce, and even more to maintain, if it really is a solution, which i'm not certain of."

I followed several tutorials on how to refresh the Excel file by Power Automate, none worked, or i failed, i don't know.

I'm going to retry later on today and show images of the problems i have while trying to setup Power Automate. If in the meanwhile anyone has a method they KNOW to work, i'm all ready to follow it.

[v-mdharahman]

Hi @LogiqueNeutre,

As you already tried using Power Automate, please consider few points and try following the mentioned steps again.

Please make sure Excel file must be stored on OneDrive or SharePoint as Power Automate cannot directly refresh an Excel file stored locally on a server. Also make sure that Power Query is used to pull data from SQL Server into Excel.

As for the steps to create the Power Automate Flow please follow:

(I assume you have already prepared the data in Excel and stored it in OneDrive or SharePoint)

* First go to Power Automate then create a Scheduled Cloud Flow for every 12 hours.

* Now add "Run script on Excel file" action and select your file. Use this Office Script

   function main(workbook:ExcelScript.Workbook) { 

    workbook.refreshAllDataConnections();

   }

* Now add "Delay" action and Set 5 minutes and wait for refresh.

* Then add "Update File Properties" action and select the same Excel file to save changes.

Now you can save & test the Flow.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

@LogiqueNeutre

[LogiqueNeutre]

Hello,

thank you for your answer.

I will be able to test this in 2 weeks and a half. It is part of my job, so you can count on me to answer then here about if it worked or not.

I thank you for your help on this matter.

Best Regards.