erge two columns, both containing multiple tabs, then split column into multiple rows

[rc_stem]

Wondering if it's possible to merge two columns, both containing multiple tabs, then split column (by tabs) into multiple rows.

Please see example:

Cashier	Customer First Name	Last Name
John	Bob Laura Michael	Smith Williams Johnson
Sally	Paul Kara Rob	Lee Levy Branch

Merge Rows:

Cashier	Customer Full Name
John	Bob Smith Laura Williams Michael Johnson
Sally	Paul Lee Kara Levy Rob Branch

Seperate tabs to create individual rows

Cashier	Customer Full Name
John	Bob Smith
John	Laura Williams
John	Michael Johnson
Sally	Paul Lee
Sally	Kara Levy
Sally	Rob Branch

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cashier", type text}, {"Customer First Name", type text}, {"Last Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform(List.Zip({Text.Split([Customer First Name],"#(lf)"),Text.Split([Last Name],"#(lf)")}), each Text.Combine(_," "))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Customer First Name", "Last Name"})
in
    #"Removed Columns"

Hope this helps.

[v-yaningy-msft]

Hi, @rc_stem 

Thanks for @NaveenGandhi reply, you can try the following steps to realize your need.

Step1: Select the two columns you want to merge and use the Merge Columns function to merge them.

Steps2: Use the Fill Down function to fill the Cashier columns.

Result: 

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[NaveenGandhi]

Hi @rc_stem 

Provide a sample data file to understand the problem better.

Regards,
NG