Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 |
Solved! Go to 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
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_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 |
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:
Table_Y wrong result:
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
Apologies for the confusing request, but thank you for resolving my issue. I realize that my mistake was trying to handle things separately.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |