cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
olebor Regular Visitor
Regular Visitor

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

Accepted Solutions

Re: Merge 2 rows in one 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

8 REPLIES 8

Re: Merge 2 rows in one row

Hi olebor.

the link to screenshot is broken

Maxim Zelensky
excel-inside.pro
olebor Regular Visitor
Regular Visitor

Re: Merge 2 rows in one row

Sorry for broken link:

 

201520152015201520152015
January            ActualFebruary       ActualMarch        ActualYTD                  ActualYTD               BudgetBudget vs
Actual

 

 I want to combine first and second row.

Re: Merge 2 rows in one 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

olebor Regular Visitor
Regular Visitor

Re: Merge 2 rows in one row

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

santt66 Regular Visitor
Regular Visitor

Re: Merge 2 rows in one row

Thanks a lot, it worked perfectly!

geethaj Visitor
Visitor

Re: Merge 2 rows in one row

Works great. Thanks for sharing 

sifar786 Member
Member

Re: Merge 2 rows in one row

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.

Funkmiester Regular Visitor
Regular Visitor

Re: Merge 2 rows in one row

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors