Forum Discussion
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 ...
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
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 |
lbendlin
Super User
Super UserThe 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.