Forum Discussion

fazza1991's avatar
fazza1991
Icon for Helper II rankHelper II
3 years ago
Solved

PowerQUery Find First Value greater than 0

Hi,   I am trying to exclusively use Power Query to find the first value in a row greater than greater than 0    Tried the below but doesnt work List.RemoveNulls(List.RemoveFirstN(Record.ToList...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, fazza1991 

        List.Select(
        List.RemoveFirstN(Record.ToList(_), 3),
        (x) => x > 0
    ){0}
  • fazza1991's avatar
    fazza1991
    3 years ago

    didnt work - keep getting error

     

    Managed to get it to work using this insteatry

     

    try List.Select( List.RemoveFirstN(Record.ToList(_), 3), (x) => x > 0 ){0}  otherwise 0)

     

    THank you though.. first part of the solution worked a treat

     

     

fazza1991's avatar
fazza1991
Icon for Helper II rankHelper II
3 years ago

Thanks for this. Almost there.

One last thing as i still get errors for those that have 0 across the entire row.

If there is no value greater than 0 how would i do it so its just 0

 

e.g. code above with iferror then 0

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago
    List.Select(
        List.RemoveFirstN(Record.ToList(_), 3),
        (x) => x > 0
    ){0} ? ?? 0
  • fazza1991's avatar
    fazza1991
    Icon for Helper II rankHelper II
    3 years ago

    didnt work - keep getting error

     

    Managed to get it to work using this insteatry

     

    try List.Select( List.RemoveFirstN(Record.ToList(_), 3), (x) => x > 0 ){0}  otherwise 0)

     

    THank you though.. first part of the solution worked a treat