Identify blank columns

[Anonymous]

Hello everybody,

I'm working on a file source Excel, with multiple columns (from A to DD let's say)

And i would like to add an additionnal column which identify the rows that have, the columns from K to EM all blank, and return "SUPPRIMER". In case there is, even one column from K to EM that is not blank, return "GARDER"

In Excel, i would have used the formula : =SI(NBVAL(K3:EM3)=0;"SUPP";"GARDER")). for example.

But in Power Query, i couldn't find the solution..

Can you help me with that ? 

Thankfully,

[AlexisOlson · Accepted solution]

You can add a custom column like this:

= Table.AddColumn(#"Changed Type", "Custom", each
    if List.IsEmpty(List.RemoveItems(Record.FieldValues(_), {null, ""}))
    then "SUPP"
    else "GARDER"
)

In the above, Record.FieldValues(_) is the list of all values in the current table row.

If you only want a certain range of these values, then use e.g. List.Range(Record.FieldsValues(_), 9, 6) to get a list of 6 elements starting at index 9. You could also use something like List.PositionOf(Record.FieldNames(_), "ColK") to find the index of ColK.

[Anonymous]

Can you please help me ?

Don't hesitate if it's not clear!

Thanks

[Anonymous]

uppp