Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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.
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
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
Hi @rc_stem
Provide a sample data file to understand the problem better.
Regards,
NG
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |