How to merge several columns named like XXX_1 XXX_2.... into only one, adding rows for each column

[dpeper6]

Hey Guys!

I need some help as I have been trying to do this by 3 days with no luck 😞

After some combining, cleaning and transformig I have table with the data I need for do the report but, power query named the columns of the same name (after a transponse transformation) with _ like: EBITDA (the original) and then EBITDA_1, EBITDA_2 to... EBITDA_3000 I would want to merge all this columns into the first one... any thoughts? 

For some clarification until the transponse the table is something like that:

Thanks in advance :)!

[v-jingzhang]

Hi @dpeper6 

What is your expected result like? Do you want something like below?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[dpeper6]

but I will need to this for all the columns? I need the one that have de primary key (CB Agrupado) in all of them....

[v-jingzhang · Accepted solution]

Hi @dpeper6 

Yes. And you may need to modify the M code in Advanced Editor manually. Here is an example. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCoMwDIZfRXpWaZOm6lG3HXrZYewmHtzUURgTtr0/a1XECmuhJLRfPvLXNSPOIQdgMTuY4d1GXR+d+8d4N+PHvolMSom56+yxBQRr4u3UqdLXYzn9IHHlGppJy+zYS//6tjfzNF3bResgXy4i7vgqLVOdOhdXOLsFoS0UNFel3mphYUUhiz8xF1Shj64rJshBiWzqQE60B4aDkVQ+vubaehU5MQBgwO1HS4g1zQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CB AGRUPADO" = _t, Concepto = _t, #"PPTO INICIAL" = _t, UPA = _t, XXXX = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CB AGRUPADO", Int64.Type}, {"Concepto", type text}, {"PPTO INICIAL", Int64.Type}, {"UPA", Int64.Type}, {"XXXX", Int64.Type}}),
    
    // Select first three columns and pivot 
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"CB AGRUPADO", "Concepto", "PPTO INICIAL"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Concepto]), "Concepto", "PPTO INICIAL"),
    
    // Select first two columns and the 4th column, then pivot
    #"Removed Other Columns 2" = Table.SelectColumns(#"Changed Type",{"CB AGRUPADO", "Concepto", "UPA"}),
    #"Pivoted Column 2" = Table.Pivot(#"Removed Other Columns 2", List.Distinct(#"Removed Other Columns 2"[Concepto]), "Concepto", "UPA"),
    
    // Select first two columns and the 5th column, then pivot
    #"Removed Other Columns 3" = Table.SelectColumns(#"Changed Type",{"CB AGRUPADO", "Concepto", "XXXX"}),
    #"Pivoted Column 3" = Table.Pivot(#"Removed Other Columns 3", List.Distinct(#"Removed Other Columns 3"[Concepto]), "Concepto", "XXXX"),
    
    // Append above three tables
    #"Append all tables" = Table.Combine({#"Pivoted Column", #"Pivoted Column 2", #"Pivoted Column 3"})
in
    #"Append all tables"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[dpeper6]

This worked well :)!! thx!!

[dpeper6]

Yep! That's exactly what I need :)!

[Greg_Deckler]

@dpeper6 Select all of the columns you want to merge while holding down the Ctrl key. Click on the Transform tab in the ribbon and then Merge Columns (in the Text Column area of the ribbon)

[dpeper6]

If I do so... it will merge the content in a single column and each of the columns must add a new line, for clarification I have added the table without transponse, thanks! 🙂

[Greg_Deckler]

@dpeper6 I missed the added rows. OK, don't merge but select your columns and unpivot?