Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using First 6 Characters in a Table to Remove Duplicates

Good afternoon,   First and foremost, I apologize for the poor question name. I wasn't sure what else to name this post.   Second, it's important to note that currently I'm unable to make changes...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Sorry for the late reply. I didn't think of a good method only using DAX at the report side. I think of a method which is a combination of Power Query and DAX. You may have a look at it. 

     

    First in Power Query Editor, add two custom columns to extract the numbers and Name length for each row. 

    let _allNumbers = Text.Select([Name], {"0".."9"}) in if Text.Length(_allNumbers)>= 10 then Text.Start(_allNumbers,10) else Text.Start(_allNumbers,6)
    Text.Length([Name])

     

    After applying the data to the model, add a new column with the following DAX. 

    Flag = 
VAR _shortnameLen = CALCULATE(MIN(SourceData[Name Length]),ALLEXCEPT(SourceData,SourceData[Numbers]))
RETURN
IF(SourceData[Name Length]=_shortnameLen,1,0)

     

    You can put this new Flag column on the table visual as a filter field to show values whose Flag is 1. 

     

    Hope it helps.

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi Anonymous 

 

Are the first six number characters unique for every article? If so, we can consider extracting them to decide whether an article is duplicated or have different sources. And the last two rows Article6 has a different name format from other articles, can we extract "3481" only or extract "979797" or extract both together to tell it's unique?

 

If you want to remove rows, usually using Power Query Editor is more suggested. On the report side, we cannot remove rows, so we usually create a measure to decide whether a value meets some conditions and filter out values that do or do not meet. The measure will play as a visual-level filter. 

 

Let me know more details about the questions in the first paragraph so I can try to provide a possible solution. 

 

Best Regards,
Community Support Team _ Jing