Using the same query in different workbooks

[Nikita Chemeris]

Hello, friends!
Could you give me a piece of advice with Get&Transform please?
In our company we have to use the information about sales in different reports, so I have some different excel files for different users. Each file contains a pivot table that comes out from the data model. And each file has some queries. But all of the files have a query that loads information about sales (I save the data about sales from our own software as txt files every month in a special folder) using the same steps.
But our IT department sometimes changes the form of report about sales (for example, they may add some new columns, rename columns etc.). And if it happens I need to delete all of the historical data and load it again (in new farmat) and to change the queries that use that data.
And the problem is that I have to keep in mind all the files where I use that queries to change it. Bur if they are identical, do I have any opportunity to change the query once in any place, so the others would change automatically?
If it possible, please try to answer in simple words, because I am not native english speakers. I hope I describe my problem quite understandable and sorry for possible mistakes.
Thanks!

[#2]

Nikita,

As i understood you have several Excel files with exactly the same query which you change from time to time. It could be several ways

1) Manually change query in each file as you do now

2) Change the query in one file and copy/paste entire query into another files

3) Keep query in Azure Data Catalog and share it from it. See more details here https://blog.crossjoin.co.uk/2016/11/29/sharing-power-query-queries-with-azure-data-catalog/?utm_sou.... It's for Power BI, same works for G&T.

Perhaps the latest will work for you.

[#3]

Thank you, Sergei!

Hope to see in future an opportunity of auto-updating of queries' codes.

The 3rd way works in Excel, but I dont see the same function in Power BI (I use both Excel and PBI), there is no any button about Data Catalog.

[#4]

Unfortunately yes, not implemented so far for Power BI Desktop. There is the workaround to use Azure Data Catalog REST API (see here https://community.powerbi.com/t5/Desktop/Azure-Data-Catalog-Integration/td-p/562/page/2 one of the latest posts), but i have no idea how it works

[#5]

Thanks again!

So, I will store all quiries in a special file and copy/paste it from here if needed and update codes from here.

[#6]

The only issue here with copy/paste of the entire query it adds new one with new name, not replaces the query. If you have dependencies it's not enough just to delete old query and rename new - all references within the model will be on old one.

I'm not sure if it's possible to replace the query with such approach.

[#7]

Hello Nikita.

I'm not sure if the link below will help but I wanted to respond because I have a similar situation and think it is going to work for me. I'm not affiliated with the website or anything, just another Excel enthusiast trying to learn how this new stuff works.

http://powerbi-pro.com/reuse-your-queries-in-power-bi/

[#8]

Hi Nolan,

Yes, that's a good point, thank you. Just in case two more related topics (not exactly about the sharing)

1) Edit external files better using Notepad++ with installed M-script definitions, google easily gives more details

2) How to export all or group of queries at once (e.g. for documenting) there is another Chris' post https://blog.crossjoin.co.uk/2017/02/25/exporting-your-queries-m-code-from-power-query-and-power-bi-...

[#9]

That is what I need! Thank you!