Help with Matrix view

[learner03]

My Input table is as below-

I need to get below output-

I tried with mtrix view but if I put Order Number in Values, it only gives me First Order number value, it does not give me all of them for same item and delivery date.

If I use Concat function, then I cannot do Conditional formatting, where I need to highlight Order Number whose status is Active.

[amitchandak]

@learner03 , You have to use Item, Order Number, on Row, Delivery Date on Column and Again Order Number on Values

[learner03]

@amitchandak Thanks it works. But, How can I make the Order Number that is in row to be hidden or no color , so that it does not show on Matrix?

[Anonymous · Accepted solution]

Hi @learner03 ,

Here I suggest you to add a rank column in Power Query Editor for each item and delivery date.

For refernce: Nested Calculations In Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLSNzDXNzAEcgyNjBOBVGJySWZZqlKsTrSSEw4lmXlIipyRFRkBOSamZsmo5mBYZWximoRujguyImMQx9gkBY97QFaZmZqk4nUPkjkoijB8huloF3TLjMwtUpGUxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Delivery Date" = _t, #"Order Nunber" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Delivery Date", type date}, {"Order Nunber", type text}, {"Status", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Item", Order.Ascending}, {"Delivery Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Item", "Delivery Date"}, {{"Rows", each _, type table [Item=nullable text, Delivery Date=nullable date, Order Nunber=nullable text, Status=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Rank",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Order Nunber", "Status", "Rank"}, {"Custom.Order Nunber", "Custom.Status", "Custom.Rank"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Order Nunber", "Order Nunber"}, {"Custom.Status", "Status"}, {"Custom.Rank", "Rank"}})
in
    #"Renamed Columns"

New table:

Create a matrix as below. Turn off "Stepped layout" in Row headers.

Then turn off word wraps and reduce the width of [Rank] in matrix to 0.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.