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
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 |
|
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.