cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jlicht Frequent Visitor
Frequent Visitor

How to append Query with the Final Applied Step Only, not all the previous applied steps.

Greetings Expert Community, 

 

I've been searching and trying to find solution for weeks, but unable to find solution in Power Query Editor. I have a Query that I've edited using "Merged Queires" to get a good Final Table set that I'm happy with. Now I want to append said query table to a different Query table, but use only the final data set (last applied step). 

 

Currently when I append, the new table has dubplicate data that is referenceing wrong data information because of the previous "Merged Query" applied steps. Logicallly this makes sense becasue PowerBI is applying the "Merged Query" applied step to the new Appended table and it's causing wrong data to be duplicated. 

 

Is there a way in Power BI that I can I can ignore the previous applied steps, and just append the Final Table  (final applied step), without carrying over. 

 

- In  Photoshop, one could "flatten" the data where as all the previous applied steps would be deleted and only the Final Table data was now the new table data without any applied steps. Does a similar function exist in PowerBI? 

- I've thought of copying the table into a new excel sheet, then reuploading it into PowerBI to get a new source table that equals my final source table, but this seems like a lot of work with 250 copy pasts to excel sheets and defeates the point of PowerBI software

- I've thougth of editing the AccessDatabase source directly, before importing to PowerBI, but isn't the point of PowerBI to allow me to edit without AccessDB. There must be a more easier way.  

 

Is there a faster option within PowerBI to use the final table data without all the Applied Steps? 

 

Table 1.PNG

 Capture 2.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jlicht Frequent Visitor
Frequent Visitor

Re: How to append Query with the Final Applied Step Only, not all the previous applied steps.

Thank you for the Suggestions. I did find a solution/workaround that allows appending mutliple quieres while still keeping unique data without duplicating wrong data references.

 

The problem was that my "FluidID" references the "TestID" but the TestID is not unique. So when I appended it, my FluidID was duplicated becasue it referenced the Test ID twice. The Duplicate data was incorrect because it belonged to a different FluidID. This happens because appending Query 1 into Query 2, then PowerBI follows all the applied steps previously in Query 2. Shown in picuture

 

The solution was to create a blank query table. After that, append both query 1 and query2  to the blank query. This allows PowerBI to only focus on the "last" applied step of each query 1 and 2.  This inturn keeps each referenced TestID to the correct FluidID in my example. So no wrong duplicates are created. Shown in picture. 

 

I'm surprised this problem isn't brought up more, because it will occur each team someone adds mutliple yearly data that have repeating ID columns that are not unique. But the solution I see is to create a blank quiery table. 

 

 

Problem with Duplicate wrong data reference after Appending Query 1 into Query 2

 

 Capture 2.png

 

Solution: Create Blank Query and append Query 1 & Query 2 into the Blank Query. 

Capture 1.png

 

 

 

 

3 REPLIES 3
Super User
Super User

Re: How to append Query with the Final Applied Step Only, not all the previous applied steps.

Going to be honest, not exactly following this. But, it seems like you should be able to use an old, old article of mine and just do everything in a single query.

 

https://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx

 

Otherwise, going to need to see some M code and the ability to recreate this some how.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: How to append Query with the Final Applied Step Only, not all the previous applied steps.

Hi @Jlicht,

 

Maybe the result isn't what you want. But it isn't caused by the applied steps. Can you share the pbix file and the expected result? You can delete most of the parts and only keep a small sample.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jlicht Frequent Visitor
Frequent Visitor

Re: How to append Query with the Final Applied Step Only, not all the previous applied steps.

Thank you for the Suggestions. I did find a solution/workaround that allows appending mutliple quieres while still keeping unique data without duplicating wrong data references.

 

The problem was that my "FluidID" references the "TestID" but the TestID is not unique. So when I appended it, my FluidID was duplicated becasue it referenced the Test ID twice. The Duplicate data was incorrect because it belonged to a different FluidID. This happens because appending Query 1 into Query 2, then PowerBI follows all the applied steps previously in Query 2. Shown in picuture

 

The solution was to create a blank query table. After that, append both query 1 and query2  to the blank query. This allows PowerBI to only focus on the "last" applied step of each query 1 and 2.  This inturn keeps each referenced TestID to the correct FluidID in my example. So no wrong duplicates are created. Shown in picture. 

 

I'm surprised this problem isn't brought up more, because it will occur each team someone adds mutliple yearly data that have repeating ID columns that are not unique. But the solution I see is to create a blank quiery table. 

 

 

Problem with Duplicate wrong data reference after Appending Query 1 into Query 2

 

 Capture 2.png

 

Solution: Create Blank Query and append Query 1 & Query 2 into the Blank Query. 

Capture 1.png