Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Split Value by deliminator and split other columns value

So I have a list of Sales reps and their commission amounts. There are a few cases where multiple sales reps worked on the same job and split the commission 50/50. How do I go about splitting the sal...
Need Help
  • Jakinta's avatar
    Jakinta
    4 years ago

    Here is one way to do it.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1UNJRctRxUorViVYyNwVznMEcCwjHCcSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Commision Amount" = _t, #"Sales Rep" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Commision Amount", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Commision Amount] / (Text.Length(Text.Select ([Sales Rep], ",")) + 1), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Commision Amount"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Sales Rep"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Commision Amount"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Sales Rep", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sales Rep")
in
    #"Split Column by Delimiter"

    Before

    After

     

     

Jakinta's avatar
Jakinta
Icon for Solution Sage rankSolution Sage
4 years ago

Here is one way to do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1UNJRctRxUorViVYyNwVznMEcCwjHCcSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Commision Amount" = _t, #"Sales Rep" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Commision Amount", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Commision Amount] / (Text.Length(Text.Select ([Sales Rep], ",")) + 1), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Commision Amount"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Sales Rep"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Commision Amount"}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Sales Rep", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sales Rep")
in
    #"Split Column by Delimiter"

Before

After

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    So I am very new to power BI/automate and am not quite sure how to interpret that. Any chance you can break it down and tell me what is going on? 

    • Jakinta's avatar
      Jakinta
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Create new blank query.

      Replace all text in new query with code above and you can follow the steps.

      The key is in the 3rd step. It is counting number of comas. Adding 1 on count we get the count of Sales Rep...

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Ahh! I see.

        I'm not sure what this json being referenced is. Am I changing the underlined section to the table I am using? EX: the table name is "Rep Commissions"

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1UNJRctRxUorViVYyNwVznMEcCwjHCcSNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Commision Amount" = _t, #"Sales Rep" = _t])