Forum Discussion
Take latest data from share point for Card Visualization
Hi afiqhaziq
You can put the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDLCsIwEEV/JWRd7MzkOdnVuitFEHel//8bxjyaRlEIhyzumbnJtkk/4khAWugAEM+0ykGSokSMhAuzcfGyPMQ6yX3oHM0BvMiSgSRBlt4Tbvd5EfjpGAgI2THJMdqURcDVomRxtjBaimK7bpP2xSLo6nFbpdRJsol1lcWfkj0kRN9Y8tevvDvyzlf8nt7SiitK+jn/SVtbgf3XnuO+VQffWAXVCxyozdcm1SGbPsghUHvw/gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, CurrentPlan = _t, CurrentActual = _t, #"Operation Rate" = _t, Section = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"CurrentPlan", Int64.Type}, {"CurrentActual", Int64.Type}, {"Operation Rate", Percentage.Type}, {"Section", type text}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type datetime}}, "en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each List.Max(Table.SelectRows(#"Changed Type with Locale", (x)=>x[Section]=[Section])[Date])), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Date] = [Custom])), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"Output
It only displayed the latest data of each section.
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
Thanks. Need few editing and works like magic
Hi afiqhaziq
You can refer to the following measure.
Measure =
VAR _filter =
FILTER ( ALLSELECTED ( 'Table' ), [Shift] IN VALUES ( 'Table'[Shift] ) )
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), _filter )
RETURN
MAXX ( FILTER ( _filter, [Date] = _maxdate ), [Operation Rate] )
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.
How to apply this to the power query though. Sorry, I dont have knowledge on DAX yet
Hi afiqhaziq
You can put the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDLCsIwEEV/JWRd7MzkOdnVuitFEHel//8bxjyaRlEIhyzumbnJtkk/4khAWugAEM+0ykGSokSMhAuzcfGyPMQ6yX3oHM0BvMiSgSRBlt4Tbvd5EfjpGAgI2THJMdqURcDVomRxtjBaimK7bpP2xSLo6nFbpdRJsol1lcWfkj0kRN9Y8tevvDvyzlf8nt7SiitK+jn/SVtbgf3XnuO+VQffWAXVCxyozdcm1SGbPsghUHvw/gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, CurrentPlan = _t, CurrentActual = _t, #"Operation Rate" = _t, Section = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"CurrentPlan", Int64.Type}, {"CurrentActual", Int64.Type}, {"Operation Rate", Percentage.Type}, {"Section", type text}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type datetime}}, "en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each List.Max(Table.SelectRows(#"Changed Type with Locale", (x)=>x[Section]=[Section])[Date])), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Date] = [Custom])), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"Output
It only displayed the latest data of each section.
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
Thanks. Need few editing and works like magic