Forum Discussion
Merging columns from the same table
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 🙂
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.⚡
2 Replies
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.⚡