Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |