Forum Discussion
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 | 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!
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
3 Replies
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"] ?? nullHi, 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!
Oh haha whoops. I'm glad that at least the second part worked.
