New column based in multiple If statements in Power Query

[WilliamAzevedo]

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	M - Término	T - Início	T - Término	N - Início	N - Término
09/04/2023 08:03:00	09/04/2023 12:00:00	09/04/2023 13:30:00	09/04/2023 17:46:00	null	null
09/04/2023 08:00:00	09/04/2023 12:00:00	09/04/2023 13:30:00	09/04/2023 17:30:00	null	null
09/04/2023 08:08:00	09/04/2023 14:38:00	null	null	null	null
26/03/2023 08:02:00	26/03/2023 12:02:00	26/03/2023 13:33:00	26/03/2023 17:33:00	null	null
null	null	null	null	04/04/2023 17:48:00	04/04/2023 19:33:00
null	null	null	null	05/04/2023 17:48:00	05/04/2023 21:48:00
01/04/2023 07:30:00	01/04/2023 12:00:00	01/04/2023 13:30:00	01/04/2023 17:58:00	null	null
null	null	null	null	05/04/2023 17:18:00	05/04/2023 22:12:00
null	null	null	null	05/04/2023 17:18:00	05/04/2023 22:12:00
15/04/2023 08:00:00	15/04/2023 12:00:00	15/04/2023 12:00:00	15/04/2023 17:45:00	null	null
16/04/2023 08:10:00	16/04/2023 12:00:00	16/04/2023 13:33:00	16/04/2023 17:30:00	null	null
null	null	null	null	13/04/2023 17:48:00	13/04/2023 21:49:00
08/04/2023 08:00:00	08/04/2023 12:00:00	08/04/2023 12:00:00	08/04/2023 17:00:00	null	null
09/04/2023 08:30:00	09/04/2023 12:00:00	09/04/2023 12:00:00	09/04/2023 16:00:00	null	null
null	null	13/05/2023 14:04:00	13/05/2023 19:33:00	null	null
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00

But I would like to do it in Power Query and it isn't working. Follows the code and the result:

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 [#"T - Início"] else [#"M - Início"]

M - Início	M - Término	T - Início	T - Término	N - Início	N - Término	Data
09/04/2023 08:03:00	09/04/2023 12:00:00	09/04/2023 13:30:00	09/04/2023 17:46:00	null	null	09/04/2023 08:03:00
09/04/2023 08:00:00	09/04/2023 12:00:00	09/04/2023 13:30:00	09/04/2023 17:30:00	null	null	09/04/2023 08:00:00
09/04/2023 08:08:00	09/04/2023 14:38:00	null	null	null	null	09/04/2023 08:08:00
26/03/2023 08:02:00	26/03/2023 12:02:00	26/03/2023 13:33:00	26/03/2023 17:33:00	null	null	26/03/2023 08:02:00
null	null	null	null	04/04/2023 17:48:00	04/04/2023 19:33:00	null
null	null	null	null	05/04/2023 17:48:00	05/04/2023 21:48:00	null
01/04/2023 07:30:00	01/04/2023 12:00:00	01/04/2023 13:30:00	01/04/2023 17:58:00	null	null	01/04/2023 07:30:00
null	null	null	null	05/04/2023 17:18:00	05/04/2023 22:12:00	null
null	null	null	null	05/04/2023 17:18:00	05/04/2023 22:12:00	null
15/04/2023 08:00:00	15/04/2023 12:00:00	15/04/2023 12:00:00	15/04/2023 17:45:00	null	null	15/04/2023 08:00:00
16/04/2023 08:10:00	16/04/2023 12:00:00	16/04/2023 13:33:00	16/04/2023 17:30:00	null	null	16/04/2023 08:10:00
null	null	null	null	13/04/2023 17:48:00	13/04/2023 21:49:00	null
08/04/2023 08:00:00	08/04/2023 12:00:00	08/04/2023 12:00:00	08/04/2023 17:00:00	null	null	08/04/2023 08:00:00
09/04/2023 08:30:00	09/04/2023 12:00:00	09/04/2023 12:00:00	09/04/2023 16:00:00	null	null	09/04/2023 08:30:00
null	null	13/05/2023 14:04:00	13/05/2023 19:33:00	null	null	13/05/2023 14:04:00
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00	23/06/2023 15:00:00
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00	23/06/2023 15:00:00
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00	23/06/2023 15:00:00
null	null	23/06/2023 15:00:00	23/06/2023 17:30:00	23/06/2023 17:30:00	23/06/2023 20:04:00	23/06/2023 15:00:00

Where in the Column "Data" is null, I expected to have "N - Início". Can anyone tell me what did I do wrong?

Thank you in advance!

[vicky_ · Accepted solution]

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]

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_]

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