Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago
Solved

Group Table with multiple conditions

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?

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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.

1 Reply

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    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.