Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Blank columns in a row

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,

Need Help
Tips and Tricks

5 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 

    if ([Column1] =null or [Column1]="") and ([Column2] =null or [Column2]="") and ([Column3] =null or [Column3]="").........and ([ColumnN] =null or [ColumnN]="") then "SUPRIMER" else "GARDER"

     you need to create a custom column like this 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your reply & the formula !

     

    Do i really have to specify indivdually all the columns? the formula will be too long as i have more than 50 columns to check... can i indicate a range of columns ?

     

    Thanks a lot !

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Anonymous  can i indicate a range of columns- doubt it, looping AlexisOlson  though for a review

      • CNENFRNL's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity Champion
        4 years ago

        In the calculation of

         

         

        Table.AddColumn(#"Prev", "New Col", each ...)

         

         

        the default context for each func is a record starting from the left-most column to the right-most column. It's easy to verify by

         

         

        Table.AddColumn(#"Prev", "New Col", each _)

         

         

         

        I personally prefer to convert it to list in the first place with Record.ToList() coz list is richer in funcs and easier to manipulate. It's also said most list-related funcs are finely tuned in the M.