Forum Discussion
Extracting unique values with a condition
Hi,
I have a column with text that looks something like this:
Fixed-Fixed-Postpaid-Fixed-OMB-OMB
Is there a formula that will return only unique values in between the dashes (-)?
Required: Fixed-Postpaid-Fixed-OMB
Hi Anonymous
Apologies, I was outside the office when I shared the solution.Yes this is a calculated column but its seems that you have some blank cells. Just modify as follows.
NewColumn = VAR String = TableName[ColumnName] VAR Items = SUBSTITUTE ( String, "-", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) VAR T3 = DISTINCT ( SELECTCOLUMNS ( T2, "@@Item", [@Item] ) ) RETURN CONCATENATEX ( T3, [@@Item], "-", [@@Item], ASC )
6 Replies
Anonymous , In Power Query Refer TextBetweenDelimiters, Also use relative position
refer example 3- https://docs.microsoft.com/en-us/powerquery-m/text-betweendelimiters#example-3
How to use in a similar video
Text Before Delimiter and Text After Delimiter: https://youtu.be/oGY4RoPKDOE
Hi Anonymous
please try
NewColumn = VAR String = TableName[ColumnName] VAR Items = SUBSTITUTE ( String, "-", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) VAR T3 = DISTINCT ( SELECTCOLUMNS ( T2, "@@Item", [@Item] ) ) RETURN CONCATENATEX ( T3, [@@Item], "-", [@@Item], ASC )Hi Anonymous
Apologies, I was outside the office when I shared the solution.Yes this is a calculated column but its seems that you have some blank cells. Just modify as follows.
NewColumn = VAR String = TableName[ColumnName] VAR Items = SUBSTITUTE ( String, "-", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) VAR T3 = DISTINCT ( SELECTCOLUMNS ( T2, "@@Item", [@Item] ) ) RETURN CONCATENATEX ( T3, [@@Item], "-", [@@Item], ASC )
