Help with Power Query to split multiple values in a cell split by comma to multiple rows

[Arun Chandramouli]

Hi All,

Hope you are doing well....I have a dataset with the combination of visitbillid and contextid denoting an encounter...Every encounter has diagnosis code ,charge codes and modifiers ...Now I have the input data as the diagnosis code seperated by commas , charge code seperated by commas and then the modifiers split by commas ..Now I require the output to have one code per row for diagnosis codes , charge code and modifiers ...Please find attached the sample excel worksheet with the input and the expected output ...Can you please help me here...

Thanks,
Arun

[#2 · Accepted solution]

@Arun Chandramouli 

We may group by first two columns without aggregation. Table within each record unpivot, transform texts to lists and combine back as table. The latest could be done by function

( tbl as table) =>

let

    #"Removed Other Columns" = Table.SelectColumns(
        tbl,
        {"Diagnosis code", "Charge code", "Modifiers"}
    ),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(
        #"Removed Other Columns",
        {}, "Attribute", "Value"
    ),
    TextsToList = Table.AddColumn(
        #"Unpivoted Columns",
        "Custom",
        each Text.Split([Value], ",")
    ),
    RemoveValueColumn = Table.RemoveColumns(TextsToList,{"Value"}),
    localTable = Table.FromColumns(
            RemoveValueColumn[Custom],
            RemoveValueColumn[Attribute] 
    )
in
    localTable

which is called by main script

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    #"Grouped Rows" = Table.Group(
        Source,
        {"VISITBILLID", "CONTEXTID"},
        { {"Count", each fnSingleTable( _ ) } }
    ),
    #"Expanded Count" = Table.ExpandTableColumn(
        #"Grouped Rows",
        "Count",
        {"Diagnosis code", "Charge code", "Modifiers"},
        {"Diagnosis code", "Charge code", "Modifiers"}
    )
in
    #"Expanded Count"

Result is

[#3]

Thank you so much @Sergei Baklan....Really appreciate your help!...Apologies that I am replying late...

[#4]

@Arun Chandramouli , you are welcome, thank you for the feedback