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

[Jlicht]

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? 

 

[Jlicht · Accepted solution]

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

 

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

[v-jiascu-msft]

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

[Greg_Deckler]

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.