Merge reference or duplicate query into source query

[filipeanalytics]

I'm trying to merge my main query with another query that I created as a reference from this main query. Power Query doesn't let me do that.

The reference query is basically a group by from the main query and I'm trying to join/merge this summary table back into the main query.

I'm trying to do the equivalent of the SUM window function in SQL.

Ex: SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @filipeanalytics , based on your description,the tables  cannot combine, because you reference the main table, it will exist the cycle reference.

you don't need to reference the main table, you can do the operations directly in main table, in your picture, you want to sum the area for a and b.

You can refer to the following code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdAxDoAwDAPAv2TuQJ0G+EuVgf7/EUQIIStk8HKqJadzCjaoNLkiI6Li7YcH43oRFY5ct0ivUHPdnhT4vQRN6hWCceWLUO0ETbIK91w3/hDGU9xv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t, Quantity = _t, Area = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Category", type text}, {"Quantity", Int64.Type}, {"Area", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Category"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Quantity", "Area", "Index"}, {"Quantity", "Area", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each List.Sum(Table.SelectRows(#"Expanded Data",(x)=>x[Year]=[Year] and x[Category]=[Category] and (x[Index]=1 or x[Index]=2))[Area]))
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[filipeanalytics]

Hi @lbendlin, I've uploaded the PBIX file in the following link: https://we.tl/t-H4KIHK56eM

This is the flow of what I'm trying to do:

I created the expected result manually in Excel.

Let me know if this makes sense.

Thank you,

Filipe

[lbendlin]

You can merge your query with itself, which is safer than using a reference query.  Read about how transform step names can be used for that.

[filipeanalytics]

Thanks a lot for the fast reply @lbendlin!

I'm trying to join my main query to a summarized version of it, so I don't think I can do that by merging the query to itself.

Is there a way to do the equivalent of a sql SUM window function?

SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary

Because in the end what I'm trying to do is to create a new column using SUM partitioned by two other columns.
Below is the image of the summarized table, which is a reference to the main query. And my goal is to merge this to the main query (which has the granular data) by year and category.

The referenced query becomes unavailable for merging when I try to do a merge from the main query:

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...