Power Query - Adding new row

Hello! Create a measure that will achieve the values whether it is a sum, count, etc.

For now, I'll just call it units: Units = SUM ('YouTable'[FieldForSumming]. Next, click the three dots next to the measure you just created and click quick measures. When the quick measure pane opens, select filtered value from the drop down. The Units measure should be in base value. For the filter part, select the field for Type and then choose P from the dropdown and click add. Next, select B from the dropdown and click add. You now have a Units for P and an Units for B measure. Next, click new measure and do Delta = [Units for P] - [Units for B].

 

In your matrix visual put your measures for P, B, and Delta in values. Go to formatting and expand Values section. Expand Options and toggle the switch so that the values will appear on Rows instead of Columns.

Hi audreygerred ,thanks for the quick reply, I'll add more.

Hi Ira_27 ,

Regarding your question, I think DAX cannot accomplish your goal effectively, so I use Power Query.

1.Add index column after grouping (for final sorting)

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-zhouwenbin\Desktop\(2)2024.9.9.xlsx"), null, true),
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PID", Int64.Type}, {"PTreeID", type text}, {"Ptype", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PID", "PTreeID"}, {{"Group", each _, type table [PID=nullable number, PTreeID=nullable text, Ptype=nullable text, A=nullable number, B=nullable number, C=nullable number, D=nullable number, E=nullable number, F=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Group],"Index",1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"PID", "PTreeID", "Group"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"PID", "PTreeID", "Ptype", "A", "B", "C", "D", "E", "F", "Index"}, {"PID", "PTreeID", "Ptype", "A", "B", "C", "D", "E", "F", "Index"}),
    #"Appended Query" = Table.Combine({#"Expanded Custom", Table2}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"PID", Order.Ascending}, {"Index", Order.Ascending}})
in
    #"Sorted Rows"

 

2.Create a new query to calculate the difference between rows

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-zhouwenbin\Desktop\(2)2024.9.9.xlsx"), null, true),
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PID", Int64.Type}, {"PTreeID", type text}, {"Ptype", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PID", "PTreeID"}, {{"Group", each _, type table [PID=nullable number, PTreeID=nullable text, Ptype=nullable text, A=nullable number, B=nullable number, C=nullable number, D=nullable number, E=nullable number, F=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Group],"Index",1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"PID", "PTreeID", "Group"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"PID", "PTreeID", "Ptype", "A", "B", "C", "D", "E", "F", "Index"}, {"PID", "PTreeID", "Ptype", "A", "B", "C", "D", "E", "F", "Index"}),
    AddColumn = Table.Group(
        #"Expanded Custom",
        {"PID", "PTreeID"},
        {
            {"Ptype", each "Delta", type text},
            {"A", each List.First([A]) - Number.Abs(List.Last([A])), type number},
            {"B", each List.First([B]) - Number.Abs(List.Last([B])), type number},
            {"C", each List.First([C]) - Number.Abs(List.Last([C])), type number},
            {"D", each List.First([D]) - Number.Abs(List.Last([D])), type number},
            {"E", each List.First([E]) - Number.Abs(List.Last([E])), type number},
            {"F", each List.First([F]) - Number.Abs(List.Last([F])), type number},
            {"Index", each List.Max([Index]) + 1 , type number}
        }
    )
in
    AddColumn

 

 

3.Append two tables and sort

4.Final output

 

Best Regards,
Wenbin Zhou