Forum Discussion
Merge 2 rows in one row
- Transpose table
- Merge two first columns with delimeter
- Transpose again
- Promote headers
- Unpivot columns
- Split columns by the same delimeter
- Pivot by second part (where parameters is)
- Njoy
Sorry for broken link:
| 2015 | 2015 | 2015 | 2015 | 2015 | 2015 |
| January Actual | February Actual | March Actual | YTD Actual | YTD Budget | Budget vs Actual |
I want to combine first and second row.
- Transpose table
- Merge two first columns with delimeter
- Transpose again
- Promote headers
- Unpivot columns
- Split columns by the same delimeter
- Pivot by second part (where parameters is)
- Njoy
Great Maxim,
This is it. I haven't used transpose, but from now on, this will be my daily function :).
Have a great day, Borut
Thanks a lot, it worked perfectly!
Works great. Thanks for sharing
This may not work with large datasets. Power Query has a limitation as it cannot handle more than 16,384 columns. So, if you have a table with more than 16,384 rows, transposing that table will lead to errors.
For larger datasets
Duplicate
Filter to keep the rows you want to merge
Then Transpose, merge,Transpose back
Append as new
Sort to bring in your header to top
Promote new header
Hi,
Really appreciate for your reply here. This really helps!
One more question, after appending the new table to the former one, what did you do to delete those rows that have been replaced with the new table?
Thanks,
Qinya
Hello,
Thank you for your solution but i tried it multiple times but i could'nt get the result i was looking for. I made an example how i have my data (the table below) and how the result needs to look like (the table above). Do you know a solution that might help?
Thank you!
Zcode1 Transmitter: ORD12344556 Reason: Company 9876 doesn't exist YCode12 Transmitter: ORD987654 Reason: Order isn't closed Transmitter: ORD12344556 ZCode1 Reason: Company 9876 doesn't exist Transmitter: ORD987654 YCode12 Reason: Order isn't closed Hi Anonymous
This case is different - you need to combine not two TOP rows, but rows pairs.
Here is the possible solution:
Step1=Table.ToColumns(Source),
Step2=List.Transform(Step1, each List.Split(_, 2)),
Step3 = List.Transform(Step2, each List.Transform(_, (pair)=>Text.Combine(pair, " "))),
Step4 = Table.FromColumns(Step3)Didn't tried it, but should work
Checked, it works, but this code works better:
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
Step1 = Table.ToColumns(Source),
Step2 = List.Transform(Step1, each List.Split(List.Transform(_, Text.From),2)),
Step3 = List.Transform(Step2, each List.Transform(_, (pair)=>Text.Combine(pair, " "))),
Step4 = Table.FromColumns(Step3)
in
Step4This code will combine rows by pairs for all columns, converting values to text to prevent errors.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Description", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",": ",":",Replacer.ReplaceText,{"Description"}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Description", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Description"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Description", type text}}), #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",":",": ",Replacer.ReplaceText,{"Description"}) in #"Replaced Value1"Hope this helps.
