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.
Hi, I have data such that many values can appear mapped to one another multiple times. In the below example, cho@gmail.com is mapped to click twice.
cho@gmail.com | click |
hint@yahoo.com | click |
hint@yahoo.com | view |
cho@gmail.com | click |
I would like to add a column to the dataset using powerquery that displays the number of click actions taken by a user. It would look something like this...
cho@gmail.com | click | 2 |
hint@yahoo.com | click | 1 |
hint@yahoo.com | view | 1 |
cho@gmail.com | click | 2 |
Is there any way to do this?
Solved! Go to Solution.
@Anonymous Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7Id0jPTczM0UvOz1XSUUrOyUzOVorViVbKyMwrcahMzMjPJyxVlplaDpbBYVwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [email = _t, #"type" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"email", type text}, {"type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"email", "type"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Table", each _, type table [email=nullable text, type=nullable text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"email", "type"}),
#"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Table", {"email", "type"}, {"Table.email", "Table.type"})
in
#"Expanded Table"
@Greg_Deckler is there a way to generalize the format to be applied to any table?
@Anonymous It's applicable to any table. You add an aggregation (Group By) step that groups by the columns you need it to group by. You have 2 aggregations. One is a Count of rows and one is "all rows". You end up with a table of your grouping columns along with the 2 aggregations. You then remove any columns other than the aggregation columns. Then you expand the aggregation column containing the Table.
@Anonymous Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7Id0jPTczM0UvOz1XSUUrOyUzOVorViVbKyMwrcahMzMjPJyxVlplaDpbBYVwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [email = _t, #"type" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"email", type text}, {"type", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"email", "type"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Table", each _, type table [email=nullable text, type=nullable text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"email", "type"}),
#"Expanded Table" = Table.ExpandTableColumn(#"Removed Columns", "Table", {"email", "type"}, {"Table.email", "Table.type"})
in
#"Expanded Table"
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |