Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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]click
[email protected]view
[email protected]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...

 

[email protected] 

click

2
[email protected]click1
[email protected]view1
[email protected]click2

 

Is there any way to do this?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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"

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
    Anonymous
    Not applicable
    3 years ago

    Greg_Deckler is there a way to generalize the format to be applied to any table?

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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.