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 a todos
Mi tabla se ve como abajo , me gustaría mantener sólo la última fila modificada en la tabla y eliminar todos los demás .
Gracias por ayudar de antemano.
Empleado | Valor | Fecha de modificación | Fecha de modificación anterior | Valor anterior |
EP123 | Sí | 22-Jun-20 | ||
EP123 | No | 23-Jun-20 | 22-Jun-20 | Sí |
EP123 | Sí | 24-Jun-20 | 23-Jun-20 | No |
EP124 | Sí | 22-Jun-20 | ||
EP124 | No | 23-Jun-20 | 22-Jun-20 | Sí |
EP125 | Sí | 21-Jun-20 | ||
EP125 | No | 22-Jun-20 | 21-Jun-20 | Sí |
EP125 | Sí | 23-Jun-20 | 22-Jun-20 | No |
EP125 | No | 24-Jun-20 | 23-Jun-20 | Sí |
Solved! Go to Solution.
@AOD iniciar una consulta en blanco, haga clic en editor avanzado y pegue el siguiente código, puede aplicar la misma lógica en la tabla
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0wNDJW0lGKTC0GkkZGul6lebpGBkC2AhjH6iDU+OWDlBgjlCArBxmArBhqoAmSaiSdQKNgik2IsN2EFNtNEQYa4jLQFGygApopyOpxmIjDfiT/mMLdisPvYJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Value = _t, #"Modified date " = _t, #"Prev modified date " = _t, #"Previous value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Value", type text}, {"Modified date ", type date}, {"Prev modified date ", type date}, {"Previous value", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Max Date", each List.Max([#"Modified date "]), type date}, {"All", each _, type table [Employee=text, Value=text, #"Modified date "=date, #"Prev modified date "=date, Previous value=text]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "Modified date ", "Prev modified date ", "Previous value"}, {"Value", "Modified date ", "Prev modified date ", "Previous value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([#"Modified date "] = [Max Date])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date"})
in
#"Removed Columns"
Me gustaría❤ elogiossi mi solución ayudara.👉Si puedes pasar tiempo publicando la pregunta, también puedes hacer esfuerzos para dar a Kudos quien haya ayudado a resolver tu problema. ¡Es una muestra de agradecimiento!
⚡Visítenos enhttps://perytus.com, su ventanilla única para proyectos/formación/consulta relacionados con Power BI.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
También puede utilizar DAX para crear una tabla.
Table 2 =
VAR tbl=ADDCOLUMNS('table (2)',"type",if('table (2)'[Modified date ]=MAXX(FILTER('table (2)','table (2)'[Employee]=EARLIER('table (2)'[Employee])),'table (2)'[Modified date ]),1,0))
return SUMMARIZE(FILTER(tbl,[type]=1),'table (2)'[Employee],'table (2)'[Value],'table (2)'[Modified date ].[Date],'table (2)'[Prev modified date ],'table (2)'[Previous value])
Proud to be a Super User!
Gracias por la solución.
He creado una nueva mesa con este DAX. Howerver, no tengo la marca de tiempo completa en la fecha modificada . Sólo está copiando la fecha.
¿Cómo añadir el tiempo también?
Tabla 2 - VAR tbl-ADDCOLUMNS('tabla (2)',"type",if('table (2)'[Fecha modificada ]-MAXX('table (2)','table (2)'[Empleado]-EARLIER('table (2)'[Empleado ])),'tabla (2)'[Fecha de modificación ]),1,0)) devolver SUMMARIZE(FILTER(tbl,[type]-1),'table (2)'[Empleado],'tabla (2)'[Valor],'tabla (2)'[Fecha modificada ].[ Fecha],'tabla (2)'[Fecha de modificación previa ],'tabla (2)'[Valor anterior])
@AOD iniciar una consulta en blanco, haga clic en editor avanzado y pegue el siguiente código, puede aplicar la misma lógica en la tabla
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0wNDJW0lGKTC0GkkZGul6lebpGBkC2AhjH6iDU+OWDlBgjlCArBxmArBhqoAmSaiSdQKNgik2IsN2EFNtNEQYa4jLQFGygApopyOpxmIjDfiT/mMLdisPvYJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Value = _t, #"Modified date " = _t, #"Prev modified date " = _t, #"Previous value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Value", type text}, {"Modified date ", type date}, {"Prev modified date ", type date}, {"Previous value", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Max Date", each List.Max([#"Modified date "]), type date}, {"All", each _, type table [Employee=text, Value=text, #"Modified date "=date, #"Prev modified date "=date, Previous value=text]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "Modified date ", "Prev modified date ", "Previous value"}, {"Value", "Modified date ", "Prev modified date ", "Previous value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([#"Modified date "] = [Max Date])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date"})
in
#"Removed Columns"
Me gustaría❤ elogiossi mi solución ayudara.👉Si puedes pasar tiempo publicando la pregunta, también puedes hacer esfuerzos para dar a Kudos quien haya ayudado a resolver tu problema. ¡Es una muestra de agradecimiento!
⚡Visítenos enhttps://perytus.com, su ventanilla única para proyectos/formación/consulta relacionados con Power BI.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |