Take latest data from share point for Card Visualization

[afiqhaziq]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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.

[afiqhaziq]

How to apply this to the power query though. Sorry, I dont have knowledge on DAX yet

[Anonymous · Accepted solution]

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

[afiqhaziq · Accepted solution]

Thanks. Need few editing and works like magic