none
Help in convert simple Excel functions to M RRS feed

  • Question

  • Hi,

    could you help me to convert this simple Excel funciton into M?

    =IF(AND(ColumnA="Active",ColumnB="",ColumnC<>"PMO",ColumnD<>"PSPM"),1,0)

    Many thanks in advance

    Ruben


    • Edited by Ruben_I-O Wednesday, January 18, 2017 8:56 AM
    Wednesday, January 18, 2017 8:56 AM

Answers

  • That would be

    = Table.AddColumn(#"Type Changed", "Custom", each
    if [ColumnA] = "Active" and ([ColumnB] = "" or [ColumnB] = null) and [ColumnC] <> "PMO" and [ColumnD] <> "PSPM" then 1 else 0)


    where #"Type Changed" is the name of the previous step.

    I included the check for nulls as empty cells in Excel will become null in Power Query.



    • Edited by MarcelBeug Wednesday, January 18, 2017 12:38 PM
    • Proposed as answer by Bertrand d'Arbonneau Wednesday, January 18, 2017 12:53 PM
    • Marked as answer by Ruben_I-O Thursday, January 19, 2017 6:03 AM
    Wednesday, January 18, 2017 9:48 AM

All replies

  • That would be

    = Table.AddColumn(#"Type Changed", "Custom", each
    if [ColumnA] = "Active" and ([ColumnB] = "" or [ColumnB] = null) and [ColumnC] <> "PMO" and [ColumnD] <> "PSPM" then 1 else 0)


    where #"Type Changed" is the name of the previous step.

    I included the check for nulls as empty cells in Excel will become null in Power Query.



    • Edited by MarcelBeug Wednesday, January 18, 2017 12:38 PM
    • Proposed as answer by Bertrand d'Arbonneau Wednesday, January 18, 2017 12:53 PM
    • Marked as answer by Ruben_I-O Thursday, January 19, 2017 6:03 AM
    Wednesday, January 18, 2017 9:48 AM
  • You might find the "M is for Data Monkey" books quite useful. Much of it is examples of Excel to M code.
    Wednesday, January 18, 2017 6:32 PM
  • Super, that worked great nice!

    Thank you very much :-)

    Thursday, January 19, 2017 6:03 AM
  • Thanks for the recommendation. - I added it to my whish-list :-)

    Thursday, January 19, 2017 6:04 AM