Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, happy new year everyone!
Could you help me with this question?
I have the table below:
col1 | col2 |
a | x |
a | y |
b | y |
b | x |
c | z |
and I want to transform it into the table below:
col1 | col3 |
a | xy |
b | yx |
c | z |
its possible? Thanks
Solved! Go to Solution.
Hi @prafael ,
Here I suggest you to use group by function and create a custom column in Power Query Editor.
= Table.Group(#"Changed Type", {"col1"}, {{"Col3", each Text.Combine([col2]), type nullable text}})
Whole M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapQitWBsCrBrCQUFkQ2GciqUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"col1"}, {{"Col3", each Text.Combine([col2]), type nullable text}})
in
#"Grouped Rows"
My Sample:
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @prafael ,
Here I suggest you to use group by function and create a custom column in Power Query Editor.
= Table.Group(#"Changed Type", {"col1"}, {{"Col3", each Text.Combine([col2]), type nullable text}})
Whole M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapQitWBsCrBrCQUFkQ2GciqUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"col1"}, {{"Col3", each Text.Combine([col2]), type nullable text}})
in
#"Grouped Rows"
My Sample:
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@prafael , This should be able to help, do not use delimiter
Concatenate Text- Measure, DAX Table, and Power Query Table: https://youtu.be/xAh3tz1qo24
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |