Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 f...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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.