Forum Discussion
Unique id for a binary column
You cannot use DAX on this table to solve this problem as presented. Even though you can see the order of the data in the data view, there is no explicit order in the actual loaded data. You could add a unique iD column before loading, but I wouldn't recommend that. Instead, you could solve it in Power Query during load. I assume you data looks like the LH column. there are different ways of solving this, but one easyish way is
add a new index column in power query (let's say it's column2)
Then add a new custom column, like
if [column1] = 0 then null else [column2]
this will then give you a new column 3 containing unique IDs
then you can click on the new column and fill down.
delete column2the new column will not be consecutive integers, but there will be unique IDs as you wanted.
It's not clear what the problem is.
Sorry Matt. I have just updated it. Please let me know if it does not make sense.
You cannot use DAX on this table to solve this problem as presented. Even though you can see the order of the data in the data view, there is no explicit order in the actual loaded data. You could add a unique iD column before loading, but I wouldn't recommend that. Instead, you could solve it in Power Query during load. I assume you data looks like the LH column. there are different ways of solving this, but one easyish way is
add a new index column in power query (let's say it's column2)
Then add a new custom column, like
if [column1] = 0 then null else [column2]
this will then give you a new column 3 containing unique IDs
then you can click on the new column and fill down.
delete column2the new column will not be consecutive integers, but there will be unique IDs as you wanted.
Thanks a lot Matt. I appreciate it. I will try it now 👍