Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.