Forum Discussion
Merge 2 rows in one row
I would like to know, if there is possibility in Power Query to combine two rows in one. I have been investigating this and I could not find the solution. What I am trying to do is in the attached p...
- 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
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
Step4
This code will combine rows by pairs for all columns, converting values to text to prevent errors.