Forum Discussion
Using First 6 Characters in a Table to Remove Duplicates
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.
Good morning,
Yes, all the article numbers are unique. Is there a way to extract or distinguish the sources outside of the Power Query editor?
Regarding the last 2 rows, ideally it would be better if all the numbers were extracted.
I have found a way to do this in the PQE, but as it frequently updates, I wanted to see if there was a way to do make those changes without it.
Thank you.
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.
Thank you for all your help! I actually did it this way as well.