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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count of number of times value is present and create a custom column

In PowerQuery I want to add a custom column that evaluates how often a value exists and create a custom column based on the value

 

For example,  new custom column(No of Rows) should be created based on how many times value exist in Column1 and if it is greater than 1 it should display as Yes or No in the column Result Column The below screen shot is what I'm trying to create 

 

I dont want to create any group/summarized table

 

blokesh23_0-1643355027551.png

 

 

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

Here you go:

Count occunrences (#"Changed Type" is the previous step):

= Table.AddColumn(#"Changed Type", "Custom", each List.Count(
Table.SelectRows(
#"Changed Type",
(A) => [Column1] = A[Column1]
)[Column1]
))


Test if over 1:

= Table.AddColumn(#"Added Custom", "Custom.1", each if [Column1] > 1 then "YES" else "NO")


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Thanks for your response . This perfectly worked for what I am trying to achieve . 

ValtteriN
Super User
Super User

Hi,

Here you go:

Count occunrences (#"Changed Type" is the previous step):

= Table.AddColumn(#"Changed Type", "Custom", each List.Count(
Table.SelectRows(
#"Changed Type",
(A) => [Column1] = A[Column1]
)[Column1]
))


Test if over 1:

= Table.AddColumn(#"Added Custom", "Custom.1", each if [Column1] > 1 then "YES" else "NO")


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




  • Hello @ValtteriN,could you tell how to modify the dax to check if we need the count the rows  for last 3 months from that row's date.?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors