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

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's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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

  • 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.

       

       

      • 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