How do I group activities of a transaction by a subsequent row value from a single column?

[commercial_user]

Basically I have a set of rows that belong to a transaction which is identified by a subsequent row value called A code ( See Fig 1 below).

For every A code I would like to group the previous rows into one group or at the very least as a new column that the transactions can be identified by the A code see (Fig 2).

I am having trouble grouping these in Power BI M query. Is there any way to group the previous rows by the transaction code?

Thank you in advance.

Fig 1.

Index   Transaction Name      Billing

1          2001-Cleaning service 200

2          2006-Water Service    400

3          3007-Inspection Service         100

4          A000001 Transaction Code    700

5          2006-Water Service    500

6          3007-Inspection Service         200

7          2009-Misc Project       200

8          2004-General Support Service           500

9          A000002 Transaction Code    1400

10        3007-Inspection Service         500

11        2001-Water Service    200

12        A000003 Transaction Code    700

13        2001-Cleaning service 200

14        2006-Water Service    200

15        3007-Inspection Service         500

16        2004-General Support Service           700

17        2005-Diag Services     1500

18        8009-Rework   100

19        9001-Misc Supplies    500

20        A000004 Transaction Code    3700

Fig 2.

Report Code   Index   Transaction Name      Billing

A000001 Transaction Code    1          2001-Cleaning service 200

A000001 Transaction Code    2          2006-Water Service    400

A000001 Transaction Code    3          3007-Inspection Service         100

A000002 Transaction Code    5          2006-Water Service    500

A000002 Transaction Code    6          3007-Inspection Service         200

A000002 Transaction Code    7          2009-Misc Project       200

A000002 Transaction Code    8          2004-General Support Service           500

A000003 Transaction Code    10        3007-Inspection Service         500

A000003 Transaction Code    11        2001-Water Service    200

A000004 Transaction Code    13        2001-Cleaning service 200

A000004 Transaction Code    14        2006-Water Service    200

A000004 Transaction Code    15        3007-Inspection Service         500

A000004 Transaction Code    16        2004-General Support Service           700

A000004 Transaction Code    17        2005-Diag Services     1500

A000004 Transaction Code    18        8009-Rework   100

A000004 Transaction Code    19        9001-Misc Supplies    500

[v-pnaroju-msft]

Hi @commercial_user,

We would like to inquire if the solution offered by @AlienSx,@p45cal and @Omid_Motamedise has resolved your issue. If you have discovered an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
Should you find the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to related queries.

Thank you.

[Omid_Motamedise · Accepted solution]

It is easy to solve using the fourth and fifth argument of Table.Group like below (The row orders is reversed)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLNasQgFIVfRVyP4I0mmSzLDAxdFEqn0EWYhaQy2AYNmrav3xh/CCWTTlaH+Ok5nmvbYsA7XFAK5NBLoZW+Iiftt+pk+I8vuxYXQVfkTYzSonMGeATYpBmlNXnUbpDdqIxeUBApPukH6j9Ar1ZoJwJ5MO8eqyNW3nIrI1BtuqXQddANeVKuQ8/WfEzoYn0fNCcnqaUVPTp/DYOx44phk4MXa8Eh1QB0M1k6DnLnf2+YskGRHdlWVcDumB7wW4Vmorwvd/V/ZTlZrL8kRyWuCXC+rXycn8DeT+hF/hj7uXgp4Btv/L3m4XmbXs270+aC5ob4WkNsDnL5BQ==", BinaryEncoding.Base64),Compression.Deflate)),type table [Index, Transaction Name, Billing]),
    #"Changed Type" = Table.ReverseRows(Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Transaction Name", type text}, {"Billing", Int64.Type}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", "Transaction Name", {{"Sum", each _}},0, (a,b)=>Number.From(Text.StartsWith(b,"A000")))
in
    #"Grouped Rows"

for more information see my video
https://youtu.be/tDfx8qSLKfc?si=LMxvJxxBNKvphede

[commercial_user]

Hi there, this comment was very helpful. I am hoping you can share some insights. I watched your video and it was great! If you can explain the arguement below

(a,b)=>Number.From(Text.StartsWith(b,"A000")))

(a,b) is this a similiar dennoation to (x,y) in your video when the function is searching for a particular value in the row?

Why would we use the argument (a,b)=>Number.From isn't this function to convert a data type to a numeric type how is this possible if the value returned is grouping the rows for each an alphanumeric type which is "A"? 

Lastly what is the significance of b or x or any character used in this expression? 

(Text.StartsWith(b,"A000")))

Thank you in advance for your kind response. 

[p45cal]

Paste the following into Power Query's Advanced Editor. It includes the data in the Source =  as the first step which you should replace with your own step:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLNasQgFIVfRVyP4I0mmSzLDAxdFEqn0EWYhaQy2AYNmrav3xh/CCWTTlaH+Ok5nmvbYsA7XFAK5NBLoZW+Iiftt+pk+I8vuxYXQVfkTYzSonMGeATYpBmlNXnUbpDdqIxeUBApPukH6j9Ar1ZoJwJ5MO8eqyNW3nIrI1BtuqXQddANeVKuQ8/WfEzoYn0fNCcnqaUVPTp/DYOx44phk4MXa8Eh1QB0M1k6DnLnf2+YskGRHdlWVcDumB7wW4Vmorwvd/V/ZTlZrL8kRyWuCXC+rXycn8DeT+hF/hj7uXgp4Btv/L3m4XmbXs270+aC5ob4WkNsDnL5BQ==", BinaryEncoding.Base64),Compression.Deflate)),type table [Index, Transaction Name, Billing]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Billing", type number}, {"Transaction Name", type text}, {"Index", Int64.Type}}),
    AddedCustom = Table.AddColumn(ChangedType, "Code", each if Text.Contains([Transaction Name],"transaction code",Comparer.OrdinalIgnoreCase) then [Transaction Name] else null),
    FilledUp = Table.FillUp(AddedCustom,{"Code"}),
    FilteredRows = Table.SelectRows(FilledUp, each not Text.Contains([Transaction Name], "transaction code",Comparer.OrdinalIgnoreCase)),
    AddedCustom3 = Table.AddColumn(FilteredRows, "Transaction Code", each Text.Trim(Text.RemoveRange([Code],Text.PositionOf([Code],"Transaction code",1,Comparer.OrdinalIgnoreCase),16))),
    #"Removed Other Columns" = Table.SelectColumns(AddedCustom3,{"Transaction Code", "Index", "Transaction Name", "Billing"})
in
    #"Removed Other Columns"

[AlienSx]

Table.ReverseRows + Table.Group with GroupKind.Local option and then do whatever you want with each group of rows.