Split Value by deliminator and split other columns value

[DSwezey]

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 sales reps out into single Reps and then also splitting the Commission amount by 50% for each Rep so I still have accurate data?

Something similar to this:

[Jakinta · Accepted solution]

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

[DSwezey]

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]

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...

[DSwezey]

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]

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.

[DSwezey]

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)