Transpose Row to columns dynamic

Anonymous,

Try this solution. In Power Query, perform the following steps:

1. Add an index column that resets for each Reference Number.

2. Create a custom column that concatenates Model # and Serial #.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc1LC4JAFAXgvxKzFrxzx5nutJNsRnyEYFQiLoSEgshFD7Bf3yTUKmx1LpfzceqaaZxzxTzGfR74CAgzWgC5RzzcL4f25C4bm5TIpNawxpsSvQMuq8SaWJEiuy4/IpBTgiuBHDXBdD1vn2M911wEVIYQFbuv0L/Eprue3yKSMrM6SzgHkmraLI/dY3C5V7gNYVUAICr8s9MP/W0c0iKLRF4JEAIdal4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference Number" = _t, Date = _t, Model = _t, #"Serial #" = _t]),
    GroupRows = Table.Group(Source, {"Reference Number"}, {{"Count", each _, type table [Reference Number=nullable number, Date=nullable datetime, Model=nullable text, #"Serial #"=nullable text]}}),
    AddIndex = Table.AddColumn(GroupRows, "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
    RemoveColumns = Table.SelectColumns(AddIndex,{"Custom"}),
    ExpandColumn = Table.ExpandTableColumn(RemoveColumns, "Custom", {"Reference Number", "Date", "Model", "Serial #", "Index"}, {"Reference Number", "Date", "Model", "Serial #", "Index"}),
    AddConcatenatedColumn = Table.AddColumn(ExpandColumn, "Model #  Serial #", each [Model] & " " & [#"Serial #"]),
    ChangeType = Table.TransformColumnTypes(AddConcatenatedColumn,{{"Reference Number", Int64.Type}, {"Date", type datetime}, {"Model", type text}, {"Serial #", type text}, {"Index", Int64.Type}, {"Model #  Serial #", type text}})
in
    ChangeType

Create measure:

Model Serial = MAX ( 'Table'[Model #  Serial #] )

Create matrix: