Group Table with multiple conditions

[joshua1990]

Hi all!

I have a table structured in this way:

Sales Nr	Sales Type	Status
1	ABC-1	A
1	ABC-1	C
1	XYZ-1	A
2	ABC-5	A
3	ABC-9	A
3	ABC-9	B
4	XYZ-6	A

Now I would like to transform / group that tablw into this structure:

1. Add 2 columns
   1. Display if Sales Type start with ABC or with XYZ
   2. Display if the Sales Type is still active
      1. Active = "A" or "C"
      2. In-Active = "B"

The result should look like this:

Sales Order	Sales Type Order	Is ABC	Is XYZ	Is Active XYZ	Is Active ABC
1	ABC-1	TRUE	FALSE	TRUE	TRUE
1	XYZ-1	FALSE	TRUE	TRUE	TRUE
2	ABC-5	TRUE	FALSE	TRUE	TRUE
3	ABC-9	TRUE	FALSE	FALSE	FALSE

A Sales Order can have multiple Sales Type Orders as you can see in Sales Order "1".

For each Sales Order and Sales Type Order combination there should be just 1 row in the end.

How would you do that in PQ?

[ImkeF · Accepted solution]

Hi @joshua1990 ,
I would do it like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0ctYF00qxOqgiznCRiMgoJDVGUDWmcBFjqIglVhEnsIgJ1BwziJpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Nr" = _t, #"Sales Type" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Nr", Int64.Type}, {"Sales Type", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"Sales Nr", "Sales Type"}, 
        {{"IsActive", each not List.Contains([Status], "B"), type nullable text}}
    ), 
    #"Inserted Text Before Delimiter" = Table.AddColumn(
        #"Grouped Rows", 
        "SalesType", 
        each Text.BeforeDelimiter([Sales Type], "-"), 
        type text
    ), 
    #"Added Custom" = Table.AddColumn(
        #"Inserted Text Before Delimiter", 
        "Custom", 
        each [
            IsABC       = [SalesType] = "ABC", 
            IsXYZ       = [SalesType] = "XYZ", 
            IsActiveXYZ = IsXYZ and [IsActive], 
            IsActiveABC = IsABC and [IsActive]
        ]
    ), 
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"IsActive", "SalesType"}), 
    #"Expanded Custom" = Table.ExpandRecordColumn(
        #"Removed Columns", 
        "Custom", 
        {"IsABC", "IsXYZ", "IsActiveXYZ", "IsActiveABC"}
    )
in
    #"Expanded Custom"

Please also check enclosed file.