CODEWITHSUNDEEP
excelpowerbipowerquerypowerbi-desktopm
IamTheB
IamTheB

Reputation: 108

Power Query: Find minimum value over multiple columns but ignoring 0 and null

I have a rather simple problem in Power Query, but I couldn't find the solution yet. I want to have an additional column with the minimum value of the other columns. But some of them contain 0 values, which I want to ignore. Like This: enter image description here

Is there an easy way with List.Min() or with a conditional column to solve this? And additional is it also possible to ignore null values? (But only needed if it does not make it more complex)

I tried something with List.Min(). Until now I just get errors or just a TRUE/FALSE like in this example: Table.AddColumn(#"Previous Step", "Min", each List.Min({[A]>0, [B]>0, [C]>0, [D]>0}), type number) I don't want to have a huge if/else statement because there are more columns than in the example above. Also I am not intrested in grouping or changing the dataset.

Thanks a lot

Upvotes: 1

Views: 1851

Answers (1)

davidebacci
davidebacci

Reputation: 30174

enter image description here

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Min( List.RemoveItems( List.RemoveNulls( Record.ToList(_)), {0})))
in
    #"Added Custom"

Upvotes: 1

Related Questions