Forum Discussion
erge two columns, both containing multiple tabs, then split column into multiple rows
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 |
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.
3 Replies
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.
Hi rc_stem
Provide a sample data file to understand the problem better.
Regards,
NGHi, 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 TeamIf 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
