Forum Discussion

eddya's avatar
eddya
Frequent Visitor
7 years ago
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 da...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

eddya's avatar
eddya
Frequent Visitor
7 years ago

Is there any way to accomplish this without using the total option? I would like to be able to rename the field as well as using conditional formatting, which is not possible in Total fields.

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,