How to calculate max and min values from a comma-separated string column in Power BI?

Question

I have a table in Power BI that contains a column with comma-separated values, like this(Spalte 2 actually has 10000 points, i have shown 10 points):

Spalte 1	Spalte 2
1	23,45,66,55,11,333,44,55,66,77
2	233,45,66,55,11,333,44,55,66,77
3	23,45,66,55,11,333,44,55,66,773
...	...

I want to add two new columns to the table that show the maximum and minimum values of each row, based on the values in the "Spalte 2" column. For example:

Spalte 1	Spalte 2	Max Value	Min Value
1	23,45,66,55,11,333,44,55,66,77	333	11
2	233,45,66,55,11,333,44,55,66,77	333	11
3	23,45,66,55,11,333,44,55,66,773	773	11
...	...	...	...

I tried using the "SPLIT" function, but it did not work. Can someone suggest a DAX formula that can achieve this? Thank you.

Max Value = MAX(VALUE(SPLIT([Spalte 2],",")))
Min Value = MIN(VALUE(SPLIT([Spalte 2],",")))

Answer 1

Here is an alternative in PQ if you want to test performance.

List.Max( List.Transform (Text.Split([Spalte 2], ","), each Number.From(_)))

Answer 2

You could possibly do this in DAX using PATH() functions but that would be a terrible use of DAX and not the right approach. Your data is in the wrong shape and needs to be reshaped in Power Query before doing analysis in DAX.

Do the following in Power Query:

Starting table:

Split column by delimiter and split to rows:

You now have 2 choices. You can either group by in Power Query:

Or leave it ungrouped, load to your model and write 2 measures as follows:

Max Measure = MAX('Table'[Spalte 2]) 
Min Measure = MIN('Table'[Spalte 2])