Expand data after a table merge with conditionals

Power Query M Code Showcase
1

Hello,

I have the following merge in power query on Power BI:

let
    Data = Table.NestedJoin(União, {"cod_parceiro"}, Hierarquia, {"cod_parceiro"}, "Hierarquia", JoinKind.LeftOuter),
    #"Hierarquia Exp" = Table.ExpandTableColumn(Data, "Hierarquia", {"grupo_de_loja", "nome_parceiro", "comercial", "regional", "potencial", "data_inicial", "data_final"}, {"grupo_de_loja", "nome_parceiro", "comercial", "regional", "potencial", "data_inicial", "data_final"})
in
#"Hierarquia Exp"

Is it possible for me to put conditionals for the expansion to happen?

For example, I need it to only return values ​​from the “Hierarquia” table if:

the field [data_pagamento] of the table “União” is >= than the field [data_inicial] of the table “Hierarquia”
and
the field [data_pagamento] of the table “União” is <= than the field [data_final] of the table “Hierarquia”

If it does not meet the conditions, return NULL for all fields in the “Hierarquia” table in the merge

2

You could filter the result of the merge using a custom column and the table.selectrows function, using the logic you included in your post, without seeing a sample file its hard to give more advice, but it is possible.

3

exemple.pbix (86.5 KB)
exemple.xlsx (117.9 KB)

Here is an example in pbix and excel

In excel I left the expected result for each invoice, which I would like to have in pbi

I added the method I got, but in excel you will realize that you would need the results as null of the values ​​that the date is not matched

Unity - União
Hierarchy - Hierarquia
payment_date - data_pagamento
initial_date - data_inicial
final_date - data_final

4 
= Table.AddColumn(#"Consultas Mescladas", "Custom", each Table.SelectRows([Tipo Alterado], (x)=> ([payment_date] >= x[#"initial_date#(lf)"] and [payment_date] <= x[#"final_date#(lf)"])))

If you add the above in a custom column it will pre select the rows in the merged table before expanding

1 Like
5

OMG you are simply a savior in my life

I tested it in the production file and it worked 110%

Thank you very much, you saved my day

6

No problems at all, glad it helped, it helps me learn as well :slight_smile:

1 Like