Power Query - Adding new row

[Ira_27]

Hello,

I am working with a dataset that has 15-20 columns and is represented in grouping and i need to create a new row but substracting two rows within each group. Can someone please help me on how to achive this in Power Query or DAX? 

Sample Data

PID	PTreeID	Ptype	A	B	C	D	E	F
1	XYZ	P	1	2	3	4	5	6
1	XYZ	B	0	1	2	3	4	5
2	ABC	P	10	20	30	40	50	60
2	ABC	B	-9	8	3	4	6	8
3	DEF	P	30	40	50	60	70	80
3	DEF	B	5	6	7	8	90	40

Expected Result

PID	PTreeID	Ptype	A	B	C	D	E	F
1	XYZ	P	1	2	3	4	5	6
1	XYZ	B	0	1	2	3	4	5
1	XYZ	Delta	1	1	1	1	1	1
2	ABC	P	10	20	30	40	50	60
2	ABC	B	-9	8	3	4	6	8
2	ABC	Delta	1	12	27	36	44	52
3	DEF	P	30	40	50	60	70	80
3	DEF	B	5	6	7	8	90	40
3	DEF	Delta	25	34	43	52	-20	40

Where Delta is difference of P and B between the same group by TreeID

[audreygerred · Accepted solution]

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.

[Anonymous · Accepted solution]

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

[audreygerred · Accepted solution]

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.

[Ira_27]

Thank you but this wont solve the problem because i have 20 different columns that needs to be presented and needs to report delta as a seperate line. This works only if i select one column in the matrix. Any other ideas?