pythonexcelpowerquerymultivalue

Expand two Excel columns, each with multiple corresponding values

How can I turn this excel table

Country Initiative Staff involved Billing hours
HK Data Lake Amy, Ben, Li 6,3,8

into this using Excel/ Python/ Power query? (not necessarily 3 staffs, can more more or less)

Country Initiative Staff involved Billing hours
HK Data Lake Amy 6
HK Data Lake Ben 3
HK Data Lake Li 8
Solution

  • Using Power Query

    You could do this using Power Query from Excel by following these steps.

    1. Select the data and go to Data>Get & Transform Data.

    2. Select the From Sheet option, make sure My table has headers is checked and click OK.

    3. In Power Query go to Add Column>Custom Column and use this for the Custom column formula.

    = Text.Split([Staff involved], ",")
    1. Go to Add Column>Custom Column again and use this for the formula.
    = Text.Split([Billing Hours], ",")
    1. Again use Add Column>Custom Column with this formula.
    = Table.FromColumns({[Custom], [Custom.1]})

    1. Now you can expand the last custom column to get the data you want.

    2. Finally delete the unneeded columns, rename the other columns as required and go to File>Close and Load to return the data to Excel.

    M Code

    Here is the full M code generated when following the above steps.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Staff involved], ",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([Billing hours], ",")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.FromColumns({[Custom], [Custom.1]})),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "Custom.2", {"Column1", "Column2"}, {"Custom.2.Column1", "Custom.2.Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.2",{"Staff involved", "Billing hours", "Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2.Column1", "Staff Involved"}, {"Custom.2.Column2", "Billing Hours"}})
in
    #"Renamed Columns"