Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

14 REPLIES 14

Hi olebor.

the link to screenshot is broken

Maxim Zelensky
excel-inside.pro
Anonymous
Not applicable

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
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
Anonymous
Not applicable

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

AliceW
Impactful Individual
Impactful Individual

A bit late to the party, but a big thank you, Funkmiester! You saved me some serious manual steps.

Anonymous
Not applicable

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!

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.