topic Extracting unique values with a condition in DAX Commands and Tips

Extracting unique values with a condition

Anonymous — Mon, 05 Sep 2022 03:00:34 GMT

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

Re: Extracting unique values with a condition

amitchandak — Mon, 05 Sep 2022 03:29:55 GMT

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

Re: Extracting unique values with a condition

Anonymous — Mon, 05 Sep 2022 03:59:12 GMT

This is what I need but unfortunately I am unable to use this method as mine is a calculated column

Re: Extracting unique values with a condition

tamerj1 — Mon, 05 Sep 2022 05:48:43 GMT

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 )

Re: Extracting unique values with a condition

Anonymous — Mon, 05 Sep 2022 06:46:15 GMT

Thanks for this. Can I use this code via "New Column"? I did that and got this error

"The arguments in GenerateSeries function cannot be blank"

Re: Extracting unique values with a condition

tamerj1 — Mon, 05 Sep 2022 07:30:59 GMT

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 )

Re: Extracting unique values with a condition

Anonymous — Mon, 05 Sep 2022 09:52:34 GMT

Thank you so much, you have made my day/week/month! I have been scouring the internet for almost a week and have not been able to find a solution.