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

[Anonymous]

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

[vanessafvg]

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

[Anonymous]

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

[vanessafvg]

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

[Anonymous]

Hi,

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

Vendor-A	10$ per Ticket
Vendor-B	10$ per Unit
Vendor-C	If qty < 10, then 10$*qty and if qty >10 then 10$
Vendor-D	If qty < 10, then 10$ per ticket and if qty >10 then 100 per ticket

DATA TABLE

Ticket	Vendor	Product	Qty	Unique	Price
1	A	KB-1	12	1
1	A	KB-2	2	0
2	A	KB-3	5	1
3	B	MO-1	23	1
4	B	KB-2	21	1
5	C	AD-1	3	1
5	C	AD-2	55	0
6	D	FI-1	2	1
6	D	KB-2	1	0

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

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,)

For more details, please check the pbix as attached.

[amitchandak]

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.