Forum Discussion

Gp2024's avatar
Gp2024
Frequent Visitor
2 years ago
Solved

Creating a column based on the value of a filtered table

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 ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
Gp2024
Frequent Visitor
2 years ago

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_codbanpfg_inivigpfg_valor
00000201/04/20247877
00000401/04/20241885
00000101/04/20244463
00000101/07/202265
00000101/07/202265
00000401/07/20222989
00000201/07/20229795
00000201/07/20229795
00000801/04/202271
00000401/09/2021142
00000701/09/20211,42
00000201/07/20219492

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

cd_classe_tensaodt_inicio_vigenciadt_fim_vigencia
AS04/07/201631/03/2017
A301/06/201626/08/2016
B230/07/202129/07/2022
A3a30/07/202229/07/2023
A3a02/03/201527/08/2015
A322/07/202321/07/2024


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

cd_classe_tensaodt_inicio_vigenciadt_fim_vigenciate_bAm
AS04/07/201631/03/20170
A301/06/201626/08/20160
B230/07/202129/07/2022142
A3a30/07/202229/07/20232989
A3a02/03/201527/08/20150
A322/07/202321/07/20242989
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
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.

 

 

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
    Gp2024
    Frequent Visitor
    2 years ago

    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

     

  • Gp2024's avatar
    Gp2024
    Frequent Visitor
    2 years ago

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