Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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...
Need Help
  • hohlick's avatar
    hohlick
    10 years ago
    1. Transpose table
    2. Merge two first columns with delimeter
    3. Transpose again
    4. Promote headers
    5. Unpivot columns
    6. Split columns by the same delimeter
    7. Pivot by second part (where parameters is)
    8. Njoy
hohlick's avatar
hohlick
Continued Contributor
10 years ago
  1. Transpose table
  2. Merge two first columns with delimeter
  3. Transpose again
  4. Promote headers
  5. Unpivot columns
  6. Split columns by the same delimeter
  7. Pivot by second part (where parameters is)
  8. Njoy
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
YCode12Transmitter: ORD987654 Reason: Order isn't closed

 

 

 Transmitter: ORD12344556
ZCode1Reason: Company 9876 doesn't exist
 Transmitter: ORD987654
YCode12Reason: Order isn't closed
 
  • hohlick's avatar
    hohlick
    Continued Contributor
    6 years ago

    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

    • hohlick's avatar
      hohlick
      Continued Contributor
      6 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.