Survey data set transform to pivot column and merge

[Anonymous]

Hi Community,

I would like to turn my survey-like data into a pivoted result table.

My raw data looks like survey reponses. One survey could have one or multiple people answered. When there are multiple people under same survey ID, they are mapped by Repeat ID. Each people has same question set. 

Below table is the sample. Survey ID 001 has aaa, bbb, ccc total 3 people answered. Survey ID 002 has single reponse.

ID	Repeat ID	Question	Response
001	aaa	State	MA
001	aaa	Cell	12234
001	aaa	Violation	Y
001	aaa	Model	civic
001	aaa	Make	honda
001	aaa	Gender	F
001	aaa	Age	40
001	bbb	State	FL
001	bbb	Cell	12543
001	bbb	Violation	N
001	bbb	Model	vw
001	bbb	Make	jetta
001	bbb	Gender	M
001	bbb	Age	29
001	ccc	State	TX
001	ccc	Cell	23445
001	ccc	Violation	Y
001	ccc	Model	cooper
001	ccc	Make	mini
001	ccc	Gender	F
001	ccc	Age	23
002	ddd	State	CA
002	ddd	Cell	246456
002	ddd	Violation	N
002	ddd	Model	honda
002	ddd	Make	CRV
002	ddd	Gender	M
002	ddd	Age	34

What I would like my data set looks like:

ID	RepeatID	State	Cell	Violation	Model	Make	Gender	Age
001	aaa	MA	12234	Y	civic	honda	F	40
001	bbb	FL	12543	N	vw	jetta	M	29
001	ccc	TX	23445	Y	cooper	mini	F	23
002	ddd	CA	246456	N	honda	CRV	M	34

And I tried to use "Piovt Column" in Power Query, but the result looks like:

ID	RepeatID	State	Cell	Violation	Model	Make	Gender	Age
001	aaa	MA	null	null	null	null	null	null
001	aaa	null	12234	null	null	null	null	null
001	aaa	null	null	Y	null	null	null	null
001	aaa	null	null	null	civic	null	null	null
001	aaa	null	null	null	null	honda	null	null
001	aaa	null	null	null	null	null	F	null
001	aaa	null	null	null	null	null	null	40
001	bbb	FL	null	null	null	null	null	null
001	bbb	null	12543	null	null	null	null	null
001	bbb	null	null	Y	null	null	null	null

Is there any way I can make my data set looks like as my expectation? 

(I can only use Power BI to build data set now. SQL is not available currently) 

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Partition = Table.Group(Source, {"Question"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ID", "Repeat ID", "Response", "Index"}, {"ID", "Repeat ID", "Response", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"ID", Order.Ascending}, {"Repeat ID", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Question]), "Question", "Response"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Hope this helps.

[PhilipTreacy]

Hi @Anonymous 

Download this sample PBIX with this transformation/code

Select the Question and Response columns then from the menu Transform tab -> Pivot Column and use these settings

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJBC4IwHIfh77KzB7MpdBTBLtmhQoroMLdRK3MRw75+G0td/OriJg9DX/8ejySOZyQijDF73RpmpF2rnJyibypk29plliRzClgr3TKjdGf3B9BKC+nOctUrjspu7pEX3QkGuJSdkE+7KYHysztG4wCapgkiyhXQGJHSOWAYsQYdIvoXki+4SmMY4FhQAfmCZBEA5zwo2O2BPgV2CDQF/DcGr+MYtH7YNwL2FXfVKbCfY/D0iRg+Z2JvhBBBRJEDDRE0o2kG+msOkw4V4f8SqI8oNjUQDGIi3+D+69Mb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Repeat ID" = _t, Question = _t, Response = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Question]), "Question", "Response")
in
    #"Pivoted Column"

Giving this

Regards

Phil

[Anonymous]

Hi Phil,

Thanks for replying. I tried this and I was given:

ID	RepeatID	State	Cell	Violation	Model	Make	Gender	Age
001	aaa	MA	null	null	null	null	null	null
001	aaa	null	12234	null	null	null	null	null
001	aaa	null	null	Y	null	null	null	null
001	aaa	null	null	null	civic	null	null	null
001	aaa	null	null	null	null	honda	null	null
001	aaa	null	null	null	null	null	F	null
001	aaa	null	null	null	null	null	null	40
001	bbb	FL	null	null	null	null	null	null
001	bbb	null	12543	null	null	null	null	null
001	bbb	null	null	Y	null	null	null	null

I would like to know how to remove all the null and merge into a single reponse. 

[PhilipTreacy]

Hi @Anonymous 

I'm not sure what you are doing to get that result. The query in my sample file works correctly.  Did you download the sample PBIX file I supplied so you could see what I did to get the table in the way you wanted it?

Can you please supply your file with the query that results in all the null values - or at least supply the query copy/paste if the file contains sensitive data.  I can use the query against the sample data you've already supplied.

Regards

Phil