Forum Discussion

WilliamAzevedo's avatar
WilliamAzevedo
Advocate II
2 years ago
Solved

New column based in multiple If statements in Power Query

Hello.   I'm trying to create a column based on three date columns, if the first is null, then look for the second and if that is also null look for the third. Here's the example:   M - Início...
Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    The very last else if is causing the issue - i think the code there should be

    if [#"N - Início"] <> null then [#"N - Início"]

    If you're interested, there's the coalesce function in PowerQuery, which might make it a bit easier to read and debug your code:

    https://gorilla.bi/power-query/coalesce/

    So in your case, you can rewrite the if statement as:

    each [#"M - Início"] ?? [#"T - Início"] ?? [#"N - Início"] ?? null
vicky_'s avatar
vicky_
Super User
2 years ago

The very last else if is causing the issue - i think the code there should be

if [#"N - Início"] <> null then [#"N - Início"]

If you're interested, there's the coalesce function in PowerQuery, which might make it a bit easier to read and debug your code:

https://gorilla.bi/power-query/coalesce/

So in your case, you can rewrite the if statement as:

each [#"M - Início"] ?? [#"T - Início"] ?? [#"N - Início"] ?? null
WilliamAzevedo's avatar
WilliamAzevedo
Advocate II
2 years ago

Hi, vicky_!

 

I changed the if functions so it reads like that:

#"Personalização Adicionada" = Table.AddColumn(#"Colunas Renomeadas", "Data", each if [#"M - Início"] <> null then [#"M - Início"] else if [#"M - Início"] = null then [#"T - Início"] else if [#"T - Início"] = null then [#"N - Início"] else if [#"N - Início"] <> null then [#"N - Início"] else [#"M - Início"])

No success, same results. No surprise if I still did something wrong.

 

In the other hand, the coalesce function worked perfectly, besides looking beautifully neat! Thank you very much for all the help!

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    Oh haha whoops. I'm glad that at least the second part worked.