Forum Discussion
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_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 |
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
10 Replies
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-Forum/ba-p/963216
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/1447523Thank 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.
