cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chandrashekarc
Helper III
Helper III

Show distinct values as 1 in new query editor column and the duplicates as 0

Hello,

 

Could you please help me with query editor formula, bringing out the desired column output (Unique) for below data table.

 

Ticket Unique

123      1 

123      1

645      0

358      0

273      1

987      0

876      0

111      1

111      1

 

Thanks,

CS

1 ACCEPTED SOLUTION

Hi @chandrashekarc ,

 

I have created a sample for your reference. 

 

1. Insert an index column in power query.

2. Create a calculated column as below.

Column = var ind = 'Table'[Index]-1
return
IF('Table'[Ticket] = CALCULATE(MAX('Table'[Ticket]),FILTER('Table','Table'[Index] = ind)),0,)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
vanessafvg
Super User I
Super User I

where are you doing this in the front end or in power query?

 

for a distinct count in DAX on the visual pane just do

 

unique =
DISTINCTCOUNT ( table[ticket] )

 

in a new measure





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

Proud to be a Super User!




Hi Vanessa,

Thanks for your reply!

 

I had created one in front end with new measure, but that doesn't show up in power query editor columns.

So I am looking at getting this output in power query new column. Because based on this, need to build other logics in power query. 

 

Regards,

CS

what are you trying to do and why do you want to create it there?   

 

you can do it via code in M in power query but its simpler to use the groupby function

 

see this thread

https://community.powerbi.com/t5/Desktop/count-distinct-on-column-in-power-query/td-p/165494





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

Proud to be a Super User!




Hi,

 

Here is the below table which might give you my purpose. I need to arrive at price based on the below conditions.

 

Vendor-A10$ per Ticket
Vendor-B10$ per Unit
Vendor-CIf qty < 10, then 10$*qty and if qty >10 then 10$
Vendor-DIf qty < 10, then 10$ per ticket and if qty >10 then 100 per ticket

 

DATA TABLE

 

TicketVendorProductQtyUniquePrice
1AKB-1121 
1AKB-220 
2AKB-351 
3BMO-1231 
4BKB-2211 
5CAD-131 
5CAD-2550 
6DFI-121 
6DKB-210 

Hi @chandrashekarc ,

 

I have created a sample for your reference. 

 

1. Insert an index column in power query.

2. Create a calculated column as below.

Column = var ind = 'Table'[Index]-1
return
IF('Table'[Ticket] = CALCULATE(MAX('Table'[Ticket]),FILTER('Table','Table'[Index] = ind)),0,)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

What is per ticket, per unit, what is diff for Vendor C <10 and > 10?

You can create a new column using Switch

SWITCH(True(),
[Vendor] ="A",10,
[Vendor] ="B",10
[Vendor] ="C" && [QTY]<10,10,
[Vendor] ="C" && [QTY]>=10,10,
[Vendor] ="D" && [QTY]<10,10,
[Vendor] ="D" && [QTY]>=10,100
)

 

if you need more help make me @

Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors