Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdata
Frequent Visitor

Shape Multiple Queries at once

Hi, If multiple queries are structured in the same way, and they each require the same reshaping (ie. several 'applied steps), is there a way to have the applied steps work on all of the sheets (maybe copy and paste the applied steps from one table to apply to the others)?  As is, I am doing the same steps on multiple queries, which is far from fun.

 

J

1 ACCEPTED SOLUTION

@jdata,

 

I just tested this and it worked fine for me:

 

1. Navigate to the "Edit Queries" section (same drop down as "data source settings") and open the Query Editor window.

 

2. You should see Source and Navigation in right pane. Under the Source step, it should look something like this (a table with the workbook information), see below:

help11.PNG

 

3. In the Navigation step, you can change the values to the new worksheet like below:

help12.PNG

 

4. Close & Apply the steps.

 

Hope this helps,

 

Alan

 

View solution in original post

6 REPLIES 6
jtdata
Regular Visitor

I tried duplicating queries and changing sources (or navigation in my case). It works but is incredibly slow if the tables are large, and since I have a lot to work with it would take a very long time. I also had shapes already using the queries that would be replaced (deleted), so having to re-create all of them would have been a major hassle.

 

After looking around a bit I stumbled across the Advanced Query Editor, which can apply all the steps at once on each query. In the Query Editor (right click on the Field and select Edit Query), go to the View tab, and select Advanced Editor. It will list out the code used in all steps, in order. Simply copy all the steps you want to duplicate (not the source or navigation) and paste them into the queries you want them to apply to. Keep in mind any steps that refer to the original Source/Navigation have to be edited to reflect the query you are currently editing. 

 

In this way you can apply all the steps to each query without having to duplicate them and replace the originals (thereby breaking any shapes using them). And there is only a single wait time as all the steps are applied, rather than waiting at the end of each step as they are calculated.

alanhodgson
Solution Supplier
Solution Supplier

Hey @jdata,

 

If you are working in PBI Desktop, then you can manipulate one of the datasets, then duplicate it and just change the "Source" step to the next query.

 

Hope this helps,

 

Alan  

Thanks @alanhodgson.  This sounds like a good solution.  But how can I change the "source"? 

 

In this case the new "source" would be another worksheet in the same excel workbook (as I try to heroiclly figure this out, but fail: when i go to "data source settings" I only see the ability to change workbooks, not worksheets).  Thanks again.

 

Jonathan

@jdata,

 

I just tested this and it worked fine for me:

 

1. Navigate to the "Edit Queries" section (same drop down as "data source settings") and open the Query Editor window.

 

2. You should see Source and Navigation in right pane. Under the Source step, it should look something like this (a table with the workbook information), see below:

help11.PNG

 

3. In the Navigation step, you can change the values to the new worksheet like below:

help12.PNG

 

4. Close & Apply the steps.

 

Hope this helps,

 

Alan

 

Got it.  Thanks @alanhodgson!

Check out this article: http://www.excelguru.ca/blog/2014/11/19/combine-multiple-worksheets-using-power-query/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.