Creating a column based on the value of a filtered table

[Gp2024]

Hello everyone,

I'm looking to create a new column that will extract values from a different table within my queries. Essentially, I have a table X with a column containing IDs, a column with dates ([pfg_inivig]), and a column with corresponding values. Additionally, I have a table Y that will store these values from table X, containing a start date, an end date, and other relevant columns.

The goal is to extract the first value from table X where the ID matches the desired value and where the date [pfg_inivig] falls within the range defined by the start and end dates of table Y. The code for this operation is provided below:

    Tabela_Bandeiras = Bandeira_Valores,
    #"Filtrar e Comparar Datas" = Table.AddColumn(#"Tipo Alterado", "te_bAm", each 
    let
        linhaAtual = _,
        DataStart = linhaAtual[dt_inicio_vigencia],
        DataEnd = linhaAtual[dt_fim_vigencia],
        bandeira = Table.SelectRows(Tabela_Bandeiras, each [pfg_codban] = "000004"),
        tab_data = Table.SelectRows(bandeira, each [pfg_inivig] >= DataStart and [pfg_inivig] <= DataEnd),
        valorBandeira = if Table.RowCount(bandeira) > 0 then bandeira{0}[pfg_valor] else null
    in
        valorBandeira, type number)
in
    #"Filtrar e Comparar Datas"

Thank you for your attention, and any assistance is appreciated. Additionally, it seems that when I write my code, the date doesn't filter correctly.

EDIT:
Tabela_Bandeira (table X) ->

pfg_codban	pfg_inivig	pfg_valor
000002	01/04/2024	7877
000004	01/04/2024	1885
000001	01/04/2024	4463
000001	01/07/2022	65
000001	01/07/2022	65
000004	01/07/2022	2989
000002	01/07/2022	9795
000002	01/07/2022	9795
000008	01/04/2022	71
000004	01/09/2021	142
000007	01/09/2021	1,42
000002	01/07/2021	9492

Table Y the original table that I want to get values from table X -> 

cd_classe_tensao	dt_inicio_vigencia	dt_fim_vigencia
AS	04/07/2016	31/03/2017
A3	01/06/2016	26/08/2016
B2	30/07/2021	29/07/2022
A3a	30/07/2022	29/07/2023
A3a	02/03/2015	27/08/2015
A3	22/07/2023	21/07/2024

Expected Result (The values of table X inserted in Table Y) ->

cd_classe_tensao	dt_inicio_vigencia	dt_fim_vigencia	te_bAm
AS	04/07/2016	31/03/2017	0
A3	01/06/2016	26/08/2016	0
B2	30/07/2021	29/07/2022	142
A3a	30/07/2022	29/07/2023	2989
A3a	02/03/2015	27/08/2015	0
A3	22/07/2023	21/07/2024	2989

[lbendlin]

It should be possible to do that a bit simpler.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Gp2024]

Thank you for the helpful suggestions. This is my first post on this forum. I have revised my initial message to offer additional details.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pc5BCsAwCATAv+QcqFoT9S0h//9GKxSSmhwK9SbDsttaAj9KOQEewAcB8f2IiqSeH+bIqFoGY2Tmeq4szt5UN9ktckQytcEU2cTKZ9Z5tbPg2myOvhGZhsqieeZXsbOx/eZ+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pfg_codban = _t, pfg_inivig = _t, pfg_valor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"pfg_inivig", type date}, {"pfg_valor", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"pfg_codban"}, {{"min date", each List.Min([pfg_inivig]), type nullable date}, {"max date", each List.Max([pfg_inivig]), type nullable date}, {"Value", each List.Sum([pfg_valor]), type nullable number}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Gp2024]

Apologies for any confusion. Let me clarify. I want to check if the data in the column [pfg_inivig] falls between the values in columns [dt_inicio_vigencia] and [dt_fim_vigencia]. If it does, I need to retrieve the first value from the column [pfg_valor] and add it to a new column in table Y called [te_bAm].

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Gp2024]

I've edited the original post with the requested information (code I'm trying to modify, tables I'm attempting to merge, and expected outcome). I hope this helps.

Tabela_Bandeira (table X) ->

pfg_codban	pfg_inivig	pfg_valor
000002	01/04/2024	7877
000004	01/04/2024	1885
000001	01/04/2024	4463
000001	01/07/2022	65
000001	01/07/2022	65
000004	01/07/2022	2989
000002	01/07/2022	9795
000002	01/07/2022	9795
000008	01/04/2022	71
000004	01/09/2021	142
000007	01/09/2021	1,42
000002	01/07/2021	9492

Table Y the original table that I want to get values from table X -> 

cd_classe_tensao	dt_inicio_vigencia	dt_fim_vigencia
AS	04/07/2016	31/03/2017
A3	01/06/2016	26/08/2016
B2	30/07/2021	29/07/2022
A3a	30/07/2022	29/07/2023
A3a	02/03/2015	27/08/2015
A3	22/07/2023	21/07/2024

Expected Result (The values of table X inserted in Table Y) ->

cd_classe_tensao	dt_inicio_vigencia	dt_fim_vigencia	te_bAm
AS	04/07/2016	31/03/2017	0
A3	01/06/2016	26/08/2016	0
B2	30/07/2021	29/07/2022	142
A3a	30/07/2022	29/07/2023	2989
A3a	02/03/2015	27/08/2015	0
A3	22/07/2023	21/07/2024	2989

[lbendlin]

The goal is to extract the first value from table X where the ID matches the desired value 

Power BI has no idea what you mean by "first".  What is your sort order?

I added some indexes but they do not match your expected result at all.

[Gp2024]

I'm sorry, I believe we're veering off track from the goal. My aim is to keep using PowerQuery to solve my issue. I've put together an Excel file with the data; I hope this helps you understand my problem. You'll see in the screenshots that Table_Y is receiving values from Table_X, but it's not working out. I'm using the following formula:

= Table.AddColumn(Type, "te_bAm", each 
    let
        linhaAtual = _,
        DataStart = linhaAtual[dt_inicio_vigencia],
        DataEnd = linhaAtual[dt_fim_vigencia],
        Flag = Table.SelectRows(Table_X, each [pfg_codban] = 4),
        tab_data = Table.SelectRows(Flag, each [pfg_inivig] >= DataStart and [pfg_inivig] <= DataEnd ),
        FlagValue = if Table.RowCount(Flag) > 0 then Flag{0}[pfg_valor] else null
    in
        FlagValue, type number)

To my surprise, in this example, even the tab_data step is working as I expected, but when I use the FlagValue step, the return isn't the expected value. As shown in the image below:
Table_X:

Table_Y:
tab_data

Table_Y wrong result:
FlagValue

[dufoq3 · Accepted solution]

Hi @Gp2024, your request is confusing. Maybe you want this but there is no [ID] connection between your tables. I've filtered [pfg_codban] = 4 at last step.

Result

let
    TableX = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc5BDsAgCATAv3g2KVAUeIvx/9+oJE206KHcyGSz21oCP0o5AV7AFwHxeERFUs8vc2RULZMxMnO9dxZnb6qH7BE5IpnaZIpsYuU367raWXBvNkffiExTZdO88qfY2dgG9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pfg_codban = _t, pfg_inivig = _t, pfg_valor = _t]),
    ChangedTypeTableX = Table.TransformColumnTypes(TableX,{{"pfg_codban", Int64.Type}, {"pfg_inivig", type date}}, "sk-SK"),
    TableY = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc49DoAgDIbhu3Qmafn401Gv4EgYvP8lbENVxpc8bemdjosCSWZpDIlVI0WWZNFoBAXJgL7VF6CybDMMnLAhmRsQDewe8A33KrCKtAiBHy4mmh8p/y+Ab0ojemQa4wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cd_classe_tensao = _t, dt_inicio_vigencia = _t, dt_fim_vigencia = _t]),
    ChangedTypeTableY = Table.TransformColumnTypes(TableY,{{"dt_inicio_vigencia", type date}, {"dt_fim_vigencia", type date}}, "sk-SK"),
    Ad_TeBam = Table.AddColumn(ChangedTypeTableY, "te_bAm", each Table.SelectRows(ChangedTypeTableX, (x)=> x[pfg_codban] = 4 and x[pfg_inivig] >= [dt_inicio_vigencia] and x[pfg_inivig] <= [dt_fim_vigencia])[pfg_valor]{0}?, type number)
in
    Ad_TeBam

[Gp2024]

Apologies for the confusing request, but thank you for resolving my issue. I realize that my mistake was trying to handle things separately.