Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |