Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Please see the data set below. There is a list of dates that duplicate. I am trying to create a calculated column that will return a true or false value if the date is unique. For instance the first time the date is recorded a 1 will be entered and the second time it appears, a 0. In excel the formulae look like so: =IF(B1094="",0,IF(COUNTIF($B$2:$B1094,B1094)>1,0,1))
I am trying to pull all added columns currently added on excel through to Power BI to eliminate the use of spreadsheets, creating a live feed to Sharepoint.
Any help is greatly appreciated, thank you in advance.
Solved! Go to Solution.
Hi @loisskinner,
Yes, please add an index column to identity each row in Query Editor by clicking "index column" as follows.
Then you can create a calculated column using the formula.
true/false = IF(Table2[Date]<>LOOKUPVALUE(Table2[Date],Table2[Index],Table2[Index]-1),1,0)
Best Regards,
Angelia
Hi @loisskinner,
I create a date column which are not same with you. Name the tale as Table2, please create a calculated column to using the formula.
unique = IF( COUNTX(FILTER(Table2,Table2[Date]=EARLIER(Table2[Date])),Table2[Date])>1,"false","Ture")
You will get the expected result. From the screenshot, only the 2017/6/7 is unique, other dates are duplicate.
Best Regards,
Angelia
Thank you for your reply. It has been very useful, but the formulae I am looking for would return a "true" for everytime a unique value is found, please see the attached data. As you can see the value in the left column appears several times but each time a new value presents a true is returned. Is there any way to incorporate this into your formulae?
Hi @loisskinner,
Yes, please add an index column to identity each row in Query Editor by clicking "index column" as follows.
Then you can create a calculated column using the formula.
true/false = IF(Table2[Date]<>LOOKUPVALUE(Table2[Date],Table2[Index],Table2[Index]-1),1,0)
Best Regards,
Angelia
That worked a treat! thank you so much