Forum Discussion
Identify blank columns
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,
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.
3 Replies
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.