cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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 print screen. I have in first row in each date column actual or budget type of values. If I try to do a unpivot other columns I lost the type (actual, budget). So I have been thinking that I could combine both rows in one and than just do the replace action on the column.

If anybody has any idea how to solve this without touching the Excel file.

Thank you

Borut

Issue

1 ACCEPTED SOLUTION

  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
Maxim Zelensky
excel-inside.pro

View solution in original post

13 REPLIES 13

Hi olebor.

the link to screenshot is broken

Maxim Zelensky
excel-inside.pro

Sorry for broken link:

 

201520152015201520152015
January            ActualFebruary       ActualMarch        ActualYTD                  ActualYTD               BudgetBudget vs
Actual

 

 I want to combine first and second row.

  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
Maxim Zelensky
excel-inside.pro

View solution in original post

Anonymous
Not applicable

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
 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Maxim Zelensky
excel-inside.pro

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.

Maxim Zelensky
excel-inside.pro

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

Works great. Thanks for sharing 

Anonymous
Not applicable

Thanks a lot, it worked perfectly!

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors