Forum Discussion

Chrisjr's avatar
Chrisjr
Icon for Helper IV rankHelper IV
3 years ago
Solved

grouping values based on a reoccuring row

Hello All,    I have the following dataset:  Brand Value Nike   Adidas    Average   Reebok     Decat   triatl     I would like to be able to categorize all th...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5PTlTSUTJVitWJVgouKMosSQVyTcBct8S8EoSkf1FiXnpmHkjAHCzgWJZalJgOUm4A1Z0I1+qcWFySA5IyA3NdgPrySuBcp9TUNLgxXqUFcG3hmXmpcAnX4pLEEoQZzhmJpSlp+UAnQRQZK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Price", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "RTM", each let i =  Table.SelectRows(#"Added Index",each [Price]=0){0}[Index]
in if [Index]<i then "Direct" else if [Index]=i then "" else "Indirect")
in
    #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Chrisjr's avatar
Chrisjr
Icon for Helper IV rankHelper IV
3 years ago

lbendlin I would then prefer with an index column. Thanks

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5PTlTSUTJVitWJVgouKMosSQVyTcBct8S8EoSkf1FiXnpmHkjAHCzgWJZalJgOUm4A1Z0I1+qcWFySA5IyA3NdgPrySuBcp9TUNLgxXqUFcG3hmXmpcAnX4pLEEoQZzhmJpSlp+UAnQRQZK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Price", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "RTM", each let i =  Table.SelectRows(#"Added Index",each [Price]=0){0}[Index]
in if [Index]<i then "Direct" else if [Index]=i then "" else "Indirect")
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".