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 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.
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:
tab_data
Table_Y wrong result:
FlagValue
- dufoq3
Community Champion
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.