Forum Discussion
How to sequence a missing key value? - For example N/As should become Missing_N
Hello dear experts,
I know this might raise some questions w.r.t data integrity, but I'm trying to understand the capabilities of Power BI, so please allow me to.
I have a problem where I don't have the "Ref Key" field, however, I have N/A (with duplicates) in the key column. So everytime I have to remove N/A to make some joins with Ref Key field.
Instead, I wish to replace it with "Missing_N" where N is a counter. That means all the N/As should become sequenced - Missing_1, Missing_2, Missing_3 without affecting the existing Ref Key. Is this possible?
Kind regards,
Ambareesh.
HI Anonymous,
I'd like to suggest you add an index column to your table at query edit side, then you can use the following calculated column to replace raw ref values:
Replaced Refkey = IF ( [Ref key] = BLANK (), " Missing_" & COUNTROWS ( FILTER ( Table, [Ref key] = BLANK () && [Index] <= EARLIER ( [Index] ) ) ), [Ref key] )Create Index Columns Using Power Query
Regards.Xiaoxin Sheng
3 Replies
Anonymous , for that you need some incremental column
add index column
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
then try a new column like
" Missing_" & countx(filter(Table, [Ref key] = earlier([Ref Key]) && [Index] < earlier([Index]) , [Ref Key])
Dear amitchandak ,
I tried the formula, but it throws an error. Kindly check again.
Expression.Error: The name 'CountX' wasn't recognized. Make sure it's spelled correctly.
Thanks,
Ambareesh
HI Anonymous,
I'd like to suggest you add an index column to your table at query edit side, then you can use the following calculated column to replace raw ref values:
Replaced Refkey = IF ( [Ref key] = BLANK (), " Missing_" & COUNTROWS ( FILTER ( Table, [Ref key] = BLANK () && [Index] <= EARLIER ( [Index] ) ) ), [Ref key] )Create Index Columns Using Power Query
Regards.Xiaoxin Sheng
