Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola Amigos,
por favor su ayuda,
Tengo una tabla con datos de costos por fechas con este formato:
Codigo Producto | Fecha | Costo |
1 | 31-11-2021 | 10566 |
1 | 05-12-2022 | 25365 |
2 | 01-05-2020 | 35 |
2 | 20-06-2021 | 55 |
2 | 22-06-2021 | 22 |
Y otra tabla con este formato:
Codigo Producto | Tipo | U.M. | Grupo | NUEVA FILA COSTO |
1 | x | Par | u | 25365 |
2 | x | Par | u | 22 |
3 | y | Unidad | v | ... |
Estoy intentando agregar una columna "NUEVA FILA COSTO" a esta tabla con el valor con la fecha más reciente...
Ultimo Costo = LOOKUPVALUE('softland iw_costop'[CostoUnitario],'softland iw_costop'[CodProd],'softland IW_vsnpProductos'[CodProd],0)
Intenté con esto, pero no se como filtrar por el último valor...
Por favor su ayuda...
Solved! Go to Solution.
You can create two blank query in Power BI, then put the following code in Advanced Editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzBCcAwDAN38bsgS669TMj+a7RxIRD6O98ZjWG0y+QiSIS/Bz2rbF47CRRycUZlF33FkVhPcViioLWUf92s1rHXvdeZpw7cjTbnAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Codigo Producto" = _t, Fecha = _t, Costo = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Codigo Producto", Int64.Type}, {"Fecha", type date}, {"Costo", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Codigo Producto"}, {{"Count", each _, type table [Codigo Producto=nullable number, Fecha=nullable date, Costo=nullable number]}, {"MaxDate", each List.Max([Fecha]), type nullable date}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Fecha", "Costo"}, {"Fecha", "Costo"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Fecha]=[MaxDate] then [Costo] else null)
in
#"Added Custom"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaoA4oDEIiBZCsRGpsZmpkqxOtFKRphyRmAJYyCzEohD8zJTElOAjDIgVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Codigo Producto" = _t, Tipo = _t, #"U.M." = _t, Grupo = _t, #"NUEVA FILA COSTO" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Codigo Producto", Int64.Type}, {"Tipo", type text}, {"U.M.", type text}, {"Grupo", type text}, {"NUEVA FILA COSTO", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"NUEVA FILA COSTO"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Codigo Producto"}, Query1, {"Codigo Producto"}, "Query1", JoinKind.LeftOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Custom"}, {"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Query1", each ([Custom] <> null))
in
#"Filtered Rows"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can create two blank query in Power BI, then put the following code in Advanced Editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzBCcAwDAN38bsgS669TMj+a7RxIRD6O98ZjWG0y+QiSIS/Bz2rbF47CRRycUZlF33FkVhPcViioLWUf92s1rHXvdeZpw7cjTbnAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Codigo Producto" = _t, Fecha = _t, Costo = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Codigo Producto", Int64.Type}, {"Fecha", type date}, {"Costo", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Codigo Producto"}, {{"Count", each _, type table [Codigo Producto=nullable number, Fecha=nullable date, Costo=nullable number]}, {"MaxDate", each List.Max([Fecha]), type nullable date}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Fecha", "Costo"}, {"Fecha", "Costo"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Fecha]=[MaxDate] then [Costo] else null)
in
#"Added Custom"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaoA4oDEIiBZCsRGpsZmpkqxOtFKRphyRmAJYyCzEohD8zJTElOAjDIgVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Codigo Producto" = _t, Tipo = _t, #"U.M." = _t, Grupo = _t, #"NUEVA FILA COSTO" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Codigo Producto", Int64.Type}, {"Tipo", type text}, {"U.M.", type text}, {"Grupo", type text}, {"NUEVA FILA COSTO", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"NUEVA FILA COSTO"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Codigo Producto"}, Query1, {"Codigo Producto"}, "Query1", JoinKind.LeftOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Custom"}, {"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Query1", each ([Custom] <> null))
in
#"Filtered Rows"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.