Forum Discussion
Filter by a alphanumeric sequence
Hi all,
In a certain table I have duplicated rows where the old ones have this format "name_XXXX-XXX_customer", and the new ones "XXXX-XXX_customer". I want to display in the report only the new ones. Which is the best approach?
My colleague who usually gives me support on this is on holidays and I'm trying to get over this before his return hoping the community could help me ;-).
Best regards and thanks in advance!
Carlos
- Perhaps not the most efficient way, but I'm trying to think of ways I can explain to you that don't require custom M code. In the Transform Data Power Query Editor:
Select the Customer column,
Click on the Add Column tab in the ribbon and choose Extract > Text Before Delimiter
Choose _ as your delimter and expand Advanced options.
Start from Right and skip 1 delimiter.
This should give you a new column with the Name portion or blank. Filter this column using the arrow to select only null or blank values.
Close and Apply your changes.
Save.
6 Replies
- Perhaps not the most efficient way, but I'm trying to think of ways I can explain to you that don't require custom M code. In the Transform Data Power Query Editor:
Select the Customer column,
Click on the Add Column tab in the ribbon and choose Extract > Text Before Delimiter
Choose _ as your delimter and expand Advanced options.
Start from Right and skip 1 delimiter.
This should give you a new column with the Name portion or blank. Filter this column using the arrow to select only null or blank values.
Close and Apply your changes.
Save.Thanks! I'll try and come back
Do you want to do this in Power Query or in DAX?
I don't have preference on doing it in power query or DAX, which do you think is the best approach?
Hi cferraz_hemav ,
You may enter into Query Editor, select on this column, go to Text Filter-> "Does not Contain", type "name_" beside the option "does not contain".
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks v-xicai but name can be any name (e.g. Las Torallas, Seuva-1,...) so I cannot use this approach
