cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bit2021
Frequent Visitor

How to find min and max value in a column with List value?

I have a table and some columns like the below:

 

Bit2021_0-1630789202043.png

I have a column with List value (item1) and I extract values with comma separate (item2) and the result is like the "pitch" column(item3).

Now I want to find Min and Max in the "pitch" column. Not each row total list. In this sample:

Max = 69.05

Min = -4.33

How can I do that? 

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @Bit2021 

 

I make a sample. You can use create two columns to show your results.

Like this:

let
    Source = {{1,2,3},{4,5,6},{7,8,9}},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Text.Combine(List.Transform([Column1],(x)=>Text.From(x)),",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Max", each List.Max(List.Combine(#"Added Custom"[Column1]))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Min", each List.Min(List.Combine(#"Added Custom"[Column1])))
in
    #"Added Custom2"

vjaneygmsft_0-1631096943890.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

View solution in original post

2 REPLIES 2
v-janeyg-msft
Community Support
Community Support

Hi, @Bit2021 

 

I make a sample. You can use create two columns to show your results.

Like this:

let
    Source = {{1,2,3},{4,5,6},{7,8,9}},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Text.Combine(List.Transform([Column1],(x)=>Text.From(x)),",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Max", each List.Max(List.Combine(#"Added Custom"[Column1]))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Min", each List.Min(List.Combine(#"Added Custom"[Column1])))
in
    #"Added Custom2"

vjaneygmsft_0-1631096943890.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

View solution in original post

hnguy71
Solution Supplier
Solution Supplier

@Bit2021 

You can use List.Min and List.Max to get the result you need. As a sample, you can create a new column with this formula to get your min and max:

Text.Combine({List.Min([roll]), List.Max([roll])}, ";")

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!