Merging columns from the same table

[Anonymous]

Hi every body

I come back to the community with a new problem 😅 (acording to my experience with Power BI)
I read some posts on the forum but they didn't seems to reflect my situation.

I have a 3 columns table looking like :

column 1 | column 2 | column 3

service A | service B | society A

service C | service D | society B
service E | service F | society C

And I would like to get a 2 columns table looking like :

column 1 | column 2 

service A | society A

service B | society A

service C | society B

service D | society B

service E | society C

service F | society C

How can I manage to get this results ?

Thanks for your help 🙂

[parry2k · Accepted solution]

@Anonymous in PQ, perform following steps:

- select col3

- right click, unpivot other columns

- remove attribute column

here is M script, start a blank query and copy the following code in advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk4tKstMTlVwVNKBs51A7PzkzNSSSqB4rA5ClTOSKhckVU4wVckgGVckVW5IqpyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", type text}, {"col3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"col3"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi parry2k

Many thanks for your time : it works fine 👍

I didn't know the trick.

Regards