cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blokesh23
Helper I
Helper I

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

2 REPLIES 2
blokesh23
Helper I
Helper I

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!




Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!