Merge 2 Columns and create new lines

[NomisNajou]

Hello,

I didn't find anything that answered my question so I'm asking it myself !

I have something that look like this :

And I would like to make it looking like this :

It's frustrating because I feel like it's really easy but I couldn't manage to make it work...

Thank you for your help and have a great day !!

NB1 : Moste the "Country 2" lines are empty and displayed ass "null" but I still want to have one single column in the end with all the datas !

NB2 : I have nearly 15 different columns of data that would be multiplied if this information changes something...

[NomisNajou · Accepted solution]

Hi,

I finnaly managed to make it work. The thing was to select both country columns and Unpivot these 2 ! 🙂

Thank you for your help 

[NomisNajou · Accepted solution]

Hi,

I finnaly managed to make it work. The thing was to select both country columns and Unpivot these 2 ! 🙂

Thank you for your help 

[VishalJhaveri · Accepted solution]

Yes, that works great. 
Another method:

Please try this below query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4uSi1XMFTSUXJOzEtMSQQyQoMdlWJ1gFIFmXkpOakKhgpwWQWYdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Country1 = _t, Country2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Country1", type text}, {"Country2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Country1] & ";" & [Country2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Country1", "Country2"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", type text}})
in
    #"Changed Type1"

 
Thank you.

If my answer helps you, please mark it as solution.

[HotChilli]

You can select the first column-> right-click and Unpivot other columns.

The way the desired table is presented (above) is going to lose the detail from the column headings (country1 or country2).  Maybe that's important to you, maybe not.

The rest of your columns will get unpivoted too and that's maybe what you want or it's going to cause chaos.

Let me know if you get stuck.