Forum Discussion
How to create a custom column that counts repeat objects in two columns.
Hi, I have data such that many values can appear mapped to one another multiple times. In the below example, [email protected] is mapped to click twice. [email protected] click [email protected] cl...
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"
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"