Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Merge 2 Columns and create new lines

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...

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
    HotChilli
    Community Champion
    3 years ago

    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.