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
Super UserIt 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/1447523
Thank you for the helpful suggestions. This is my first post on this forum. I have revised my initial message to offer additional details.
- lbendlin
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].
- lbendlin
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.