Forum Discussion
Creating a column based on the value of a filtered table
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
Thank you for the helpful suggestions. This is my first post on this forum. I have revised my initial message to offer additional details.
Super Userlet
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].
- lbendlin
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