Split column by delimiter into rows, iterate an ID, and divide a column by number of split rows

[RookieEngineer]

Posting as I have been struggling with this. 

Input (there are other columns in this table, but these are the ones that need transformation.)

ID	Name	Count
FF12/33	John, Jame	4
FF66/D2	Bob	1
FF13/4F	Carter, Carlie, Charlie, Charlie, Cat	5
FF0/45	Zed, Zeus	2
FFLL-34	Harry	6

Things that need to happen:

1. Split column Name by delimiter (,) into rows
2. For each row it is split into:
   1. ID will become unique by appending " - #" to the existing ID, where # is the count of the number of rows split into 
      1. Preferably where there is only one Name, there is no need to append " - 1", if this is too complicated, that's fine
   2. Count will become Count/number of rows the delimiter has split

Expected output:

ID	Name	Count
FF12/33 - 1	John	2
FF12/33 - 2	Jame	1
FF66/D2	Bob	1
FF13/4F - 1	Carter	1
FF13/4F - 2	Carlie	1
FF13/4F - 3	Charlie	1
FF13/4F - 4	Charlie	1
FF13/4F - 5	Cat	1
FF0/45 - 1	Zed	1
FF0/45 - 2	Zeus	1
FFLL-34	Harry	6

Thank you so much for your time and help.

[AlienSx]

let
    to_list = Table.ToList(
        Source, 
        (x) => ((names) => 
            List.Zip(
                {
                    if names[count] = 1 then {x{0}} else List.Transform({1..names[count]}, (w) => x{0} & Text.Format(" - #{0}", {w})),
                    names[names], 
                    List.Repeat({x{2} / names[count]}, names[count])
                }
            )
        )([names = Text.Split(x{1}, ", "), count = List.Count(names)])
    ), 
    to_table = Table.FromList(List.Combine(to_list), (x) => x, Table.ColumnNames(Source))
in
    to_table

[V-yubandi-msft]

Hi @RookieEngineer,

Thanks for reaching out to the Microsoft Fabric Community Forum.

• Start by loading your data into Power Query and selecting the names column. In the Transform tab, click Split Column-By Delimiter, choosing Comma, then Split into Rows.
• Now, each name will be in its own row, but ID and Count will be repeated. Create a sequence number by going to Add Column and selecting Index Column - From 1.
• Generate an index starting from 1 and modify the ID column by appending the sequence number. Select ID, go to Add Column, and use Custom Column with the provided M code.
• This code adds - # to ID for rows where Index is over 1, retaining the original ID for single names. Group by original ID in Power Query, adding a custom column to count rows per group.
• Divide the original Count by the number of rows using a custom column with the formula provided. After calculating, expand the table back to individual rows using the expand button.
• You’ll have the Name, ID with sequence numbers, and the new Count. Finally, clean up by removing the Index column and any unnecessary columns.
  
  

If my response solved your query, please mark it as the Accepted solution to help others find it easily!

And if my answer was helpful, I'd really appreciate a 'Kudos'

[Omid_Motamedise]

Hi @RookieEngineer 

you can use this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnMzNNI3NlbSUfLKz8jTUfBKzE0FckyUYnVAkmZm+i5GQL5TfhKQNISKGhrrm7gB+c6JRSWpRToKQDonMxVIZ2AwEkuA6kyh+gz0TUyB3KjUFB2FqNTSYiDbCCrl46NrbALkeyQWFVUCaTPc4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Name],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "New Count", each [Count]/List.Count([Custom])),
    #"Multiplied Column" = Table.TransformColumns(#"Added Custom1", {{"Custom", each if List.Count(_)=1 then _ else List.Transform(List.Positions(_), (x)=> Text.From(x+1) & " " & _{x})}}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Multiplied Column", "Custom")
in
    #"Expanded Custom"

[RookieEngineer]

thank you for your response. Unfortunately I ended up with this!

[BA_Pete · Accepted solution]

Hi @RookieEngineer ,

Try the following example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnMzNNI3NlbSUfLKz8jTUfBKzE0FckyUYnVAkmZm+i5GQL5TfhKQNISKGhrrm7gB+c6JRSWpRToKQDonMxVIZ2AwEkuA6kyh+gz0TUyB3KjUFB2FqNTSYiDbCCrl46NrbALkeyQWFVUCaTOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Count = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),

// Relevant steps from here ====>
    splitNameByDelim = Table.ExpandListColumn(Table.TransformColumns(chgTypes, {{"Name", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name"),
    groupIDNested = Table.Group(splitNameByDelim, {"ID"}, {{"data", each _, type table [ID=nullable text, Name=nullable text, Count=nullable number]}}),
    addID_Index = Table.TransformColumns(groupIDNested, {"data", each Table.AddIndexColumn(_, "ID_Index", 1, 1)}),
    addID_SplitCount = Table.AddColumn(addID_Index, "ID_SplitCount", each Table.RowCount([data])),
    expandData = Table.ExpandTableColumn(addID_SplitCount, "data", {"Name", "Count", "ID_Index"}, {"Name", "Count", "ID_Index"}),
    replaceID = Table.ReplaceValue(expandData,each [ID], each if [ID_SplitCount] = 1 then [ID] else Text.Combine({[ID], Text.From([ID_Index])}, " - ") ,Replacer.ReplaceText, {"ID"}),
    addNewCount = Table.AddColumn(replaceID, "NewCount", each [Count] / [ID_SplitCount]),
    remOthCols = Table.SelectColumns(addNewCount,{"ID", "Name", "NewCount"})
in
    remOthCols

It transforms this:

...into this:

Pete

[RookieEngineer]

Hello Pete, thank you. I've run this and can confirm I'm getting the desired output. 

[Poojara_D12]

Hi @RookieEngineer 

To transform the table in Power Query:

Split Names into Rows:

Select the Name column → Split Column → By delimiter , → Split into Rows.

Add Index:

Use Group By on the ID column → Add Index within each group.

Adjust ID:

Add a column: Update ID to append - # for rows with multiple names.

Adjust Count:

Divide the Count by the number of rows per ID group.

Cleanup:

Remove unnecessary columns and apply the changes.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS