Merge reference or duplicate query into source query

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.