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

     

     

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])

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

      Since you have not provided the table, I had to create one. The json is that very table.

      I had to start from somewhere. 🙂

      You can copy/paste steps you need. Just change the referencing steps to steps in your query and it will work.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Or course haha! So where exactly do I pull from my "Rep Commissions" table in that code? AKA what am I replacing? (sorry, have never dealt with coding like this before)