Merge 2 rows in one row

[Anonymous]

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

[hohlick]

Hi olebor.

the link to screenshot is broken

[Anonymous]

Sorry for broken link:

2015	2015	2015	2015	2015	2015
January            Actual	February       Actual	March        Actual	YTD                  Actual	YTD               Budget	Budget vs Actual

 I want to combine first and second row.

[hohlick · 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

[Anonymous]

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

Transmitter: ORD12344556 ZCode1 Reason: Company 9876 doesn't exist   Transmitter: ORD987654 YCode12 Reason: Order isn't closed

[Ashish_Mathur]

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.

[hohlick]

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]

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.

[Anonymous]

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]

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

[Ola_data]

It worked. You're a star! Appreciate it 🙂

[AliceW]

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

[Anonymous]

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

[geethaj]

Works great. Thanks for sharing 

[Anonymous]

Thanks a lot, it worked perfectly!

[Anonymous]

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