Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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, cho@gmail.com  is mapped to click twice.

 

cho@gmail.comclick
hint@yahoo.comclick
hint@yahoo.comview
cho@gmail.comclick

 

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.comclick1
hint@yahoo.comview1
cho@gmail.comclick2

 

Is there any way to do this?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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"

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@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"

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.