Transpose Row to columns dynamic

Hi Anonymous ,

Agree with ryan_mayu ,
To achieve this:

Firstly, it is possible that for a new reference number there will be 5 values of  Model & series. In this case these these 2 extra columns (for 4 & 5 were not pulled in the report table, they will never apper in the report.

Secondly, it may be possible that for a given dataset count value of Model & series is 2 only. In this case report will give error as column Model 3 doesnot exist in the data model.

You must have a table that contains all possible column names, because Power Query cannot automatically add or delete columns and modify column names based on the existing number of columns when new data is entered or deleted. You must have a table to fix all possible column names.

So if ryan_mayu's reply can help you, please remember to accept ryan_mayu's reply as solution to help the other members find it more quickly. Thank you!

Best Regards,
Dino Tao

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: