Forum Discussion
Solved
Matrix To Show Latest Order Date Per Line
Hello,
I would like to have displayed the latest order date in a matrix I created without using the Subtotal/Grand Total options so that I will be able to use conditional formatting on the max date as well. Here is a screenshot of my expected results:
thank you!
Hi eddya
You may get it in query editor.Then add an index column to sort the store column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZDLEsMgCEX/hXUcipqULNum73faneP//0ZIaMWZuziAVy+mBJvtbnDkRdAAIaFfUT+jx7AUrEUsrNhDbhLsD8eTa6NIJhGp2CPWWDj8LlX3+XK9LW+rm+1ULDGwtW5lvT+eL9exSCYS9Z8u1EiGrDhb3+Pn63wQ2cad4dqQ6y/JeQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductID = _t, Store1 = _t, Store2 = _t, Store3 = _t, Store4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", type text}, {"Store1", type date}, {"Store2", type date}, {"Store3", type date}, {"Store4", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Max({[Store1],[Store2],[Store3],[Store4]})), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Last Sold Overall"}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"ProductID"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Store"}, {"Value", "Date"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "Index", each if [Store] = "Store1" then 1 else if [Store] = "Store2" then 2 else if [Store] = "Store3" then 3 else if [Store] = "Store4" then 4 else 5) in #"Added Conditional Column"Regards,
