Transpose Row to columns dynamic

[kaustubhgh]

Hi All,

I have a requirement where I need to show values of Model & Serial # in columns for each Reference Number

Raw data:

Reference Number	Date	Model	Serial #
92716	1/14/2020 8:08	Hyundai	GHFK88FKGF
92716	1/14/2020 8:08	Honda	YJGFH6868GNS
92745	1/14/2020 8:08	Honda	163212980
92745	1/14/2020 8:08	Mazda	M91348SA0DPW
92749	1/14/2020 8:08	Tesla	D55LG9LJ110856
92749	1/14/2020 8:08	Chevy	X62VA0EP002262
92749	1/14/2020 8:08	Toyota	D93LD3MY303322

Expected output:

Reference Number	Date	Model 1*	Serial # 1*	Model 2*	Serial # 2*	Model 3*	Serial # 3*
92716	1/14/2020 8:08	Hyndai	GHFK88FKGF	Honda	YJGFH6868GNS
92745	1/14/2020 8:08	Honda	163212980	Mazda	M91348SA0DPW
92749	1/14/2020 8:08	Tesla	D55LG9LJ110856	Chevy	X62VA0EP002262	Toyota	D93LD3MY303322

I am able to achive this by implementing the steps mentioned in below post 

https://community.fabric.microsoft.com/t5/Desktop/converting-rows-into-column-in-powerquery/td-p/295...

However the problem occures when the count of Model & series increases/decrease from 3 from the sample data.

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.

So, is there any way available to overcome above limitations. Can this requirement be achived by using DAX functions at report level.

Thnak you for the help.

[v-junyant-msft]

Hi @kaustubhgh ,

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

[ryan_mayu · Accepted solution]

@kaustubhgh 

you need create two assist tables which contains enough column names.

pls see the attachemnt below

[DataInsights]

@kaustubhgh,

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:

[kaustubhgh]

Hi @DataInsights ,

Thank you for providing the solution. In you colution you have concatinated Model & Serial to display in one column. However my requirement is to show Model & Serial in two seperate columns.

Is there any way to achive that?