Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello to all
I have a table that is like this:
id fieldid issueid label
1 1 1 a
2 2 1 b
3 1 1 c
4 3 2 d
5 2 3 e
6 1 4 f
and I need to get to this:
id fieldid issueid label
1 1 1 a c
2 2 1 b
4 1 2 d
5 2 2 e
6 1 3 f
I need to join labels both by fieldid and issueid maintaining the rest the same.
I manage to do a merge by issueid without man problems but i mix the fieldid...
Can this be done? (I'm using M)
Thanks
Solved! Go to Solution.
Hi @Anonymous,
Hi,
In your screenshot, the [filedid] and [issueid] in your expected table is different from your raw table I guess it should return the [filedid] and [issueid] same as raw table like below, right?
id fieldid issueid label
1 1 1 a c
2 2 1 b
4 3 2 d
5 2 3 e
6 1 4 f
Based on my test,you can utilize Table.Group function like below:
Table.Group(#"Changed Type", {"fieldid", "issueid"}, {{"Count", each Text.Combine([label],",")}, {"minid", each List.Min([id]), type number}})
You can open the “Advanced Editor” and enter the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeNEpVidaCUjIMsIKpIEFjFGUpMMFjEBsoyh6lLAIqZQHkg0FSxiBtUBUpumFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, fieldid = _t, issueid = _t, label = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"fieldid", Int64.Type}, {"issueid", Int64.Type}, {"label", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"fieldid", "issueid"}, {{"Count", each Text.Combine([label],",")}, {"minid", each List.Min([id]), type number}})
in
#"Grouped Rows"
Click the “done” and you can see the result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/kf06vvjdcjwuhhl/Group%20a%20column%20based%20on%20two%20ids2.pbix?dl=0
Regards,
Daniel He
Hi @Anonymous,
Hi,
In your screenshot, the [filedid] and [issueid] in your expected table is different from your raw table I guess it should return the [filedid] and [issueid] same as raw table like below, right?
id fieldid issueid label
1 1 1 a c
2 2 1 b
4 3 2 d
5 2 3 e
6 1 4 f
Based on my test,you can utilize Table.Group function like below:
Table.Group(#"Changed Type", {"fieldid", "issueid"}, {{"Count", each Text.Combine([label],",")}, {"minid", each List.Min([id]), type number}})
You can open the “Advanced Editor” and enter the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeNEpVidaCUjIMsIKpIEFjFGUpMMFjEBsoyh6lLAIqZQHkg0FSxiBtUBUpumFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, fieldid = _t, issueid = _t, label = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"fieldid", Int64.Type}, {"issueid", Int64.Type}, {"label", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"fieldid", "issueid"}, {{"Count", each Text.Combine([label],",")}, {"minid", each List.Min([id]), type number}})
in
#"Grouped Rows"
Click the “done” and you can see the result.
You can also download the PBIX file to have a view.
https://www.dropbox.com/s/kf06vvjdcjwuhhl/Group%20a%20column%20based%20on%20two%20ids2.pbix?dl=0
Regards,
Daniel He
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |