Search code examples
excelpowerbipowerquerypowerbi-desktopm

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

Solution

  • 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"