Forum Discussion
Take latest data from share point for Card Visualization
Hi,
I have the data card as below that display efficiency for each section.
My sharepoint list as below
Currently, I have duplicate error because in the SP list have multiple date with same section.
What i need is only for the data card to show the latest data for each section. Anyone has suggestion.
Thank you
P/S : I update data multiple times per day
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
4 Replies
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