Refresh Excel Power Query data from Power BI

[JMDEJESUS88]

Hello everyone,

I created a custom API query in Excel Power Query to pull my data. I want to link this to my PowerBI without replicating the queries in Power BI because the API query takes a lot of data and it is from different excel workbooks. Also, its taking a long time to Query so I decided to place everything in Excel and then I import the excel workbook to my Power BI desktop. When I refresh my PowerBI, it does not refresh my PowerQuery in Excel. The only way to refresh data from PowerBi is to refresh the individual PowerQuery in Excel then refresh PowerBI. Is there a way to make my Excel Query refresh when I do a refresh from PowerBI?

Would appreciate any help you have.

Thanks

[BA_Pete · Accepted solution]

Hi @JMDEJESUS88 ,

No, Power BI can't send an instruction to Excel to refresh its queries, that's why Power BI has Power Query integrated.

Put your API query into PBI PQ, it will take less time as you will just be running the query then sending to the data model, rather than running a query, pulling the data over from Excel, then sending to the data model.

Additionally, the work for the PBI refresh will be picked up by your gateway machine once in production. This should have more available resources for processing and, therefore, will be faster again.

Pete

[JMDEJESUS88]

Thanks Pete, I guess I got no choice but to migrate all my Queries to PowerBI (should be easy by doing an import). The goal of this is to have a common dataset for the API Query I did and can be utilized by any users who's gonna pull it from our workspace.

[BA_Pete]

Hi @JMDEJESUS88 ,

Moving queries from Excel to Power BI is as easy as selecting all queries in Excel PQ, right-click or Ctrl+C, go to PBI PQ then right-click on the empty query area and select paste.

Best of luck! 🙂

Pete