Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Gp2024
Frequent Visitor

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 ([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_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
1 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

dufoq3_0-1710946331020.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

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

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

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.

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

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

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

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.

 

lbendlin_0-1710898361213.png

 

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:

Gp2024_0-1710940080768.png

Table_Y:
tab_datatab_data

Table_Y wrong result:
FlagValueFlagValue

 

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

dufoq3_0-1710946331020.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Gp2024
Frequent Visitor

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.