Manipulate table column / row with same ID

[threw001]

Hi guys,

I have the below Table A where the ID's are repeated based on the field 'Question Name'. For example Form ID A001 and A002 are repeated 5 times due to 5 questions being answered in the form. Form ID A003 is only repeated 4 times as the 'Question Name' 'Prefix' was never answered in Form ID A003.

I would like Power Query to Reorder the data as per Table B. I would like to handpick columns transposed, i.e. I would like a column for 'Hair Colour', 'Car Type', 'State' and 'Prefix'. I do not need the data field 'Country' so this does not have to be carried into Table B. Form ID A003 did not have a column entry for question name 'Prefix' and therefore this is null.

Is there a way to do this in PowerQuery? 

Thank you so much!

Table A

Form ID	Client ID	Question Name	Question Answer	Form Submitted Date
A001	1550	Hair Colour	Black	08/03/2024
A001	1550	Car Type	Sedan	08/03/2024
A001	1550	State	VIC	08/03/2024
A001	1550	Country	Australia	08/03/2024
A001	1550	Prefix	X12	08/03/2024
A002	1551	Hair Colour	White	01/03/2024
A002	1551	Car Type	Wagon	01/03/2024
A002	1551	State	NSW	01/03/2024
A002	1551	Country	Australia	01/03/2024
A002	1551	Prefix	X12	01/03/2024
A003	1552	Hair Colour	Grey	01/03/2024
A003	1552	Car Type	Sedan	01/03/2024
A003	1552	State	VIC	01/03/2024
A003	1552	Country	Australia	01/03/2024

Table B

Form ID	Client ID	Form Submitted Date	Hair Colour	Car Type	State	Prefix
A001	1550	08/03/2024	Black	Sedan	VIC	X12
A002	1551	01/03/2024	White	Wagon	NSW	X12
A003	1552	01/03/2024	Grey	Sedan	VIC

[v-xinruzhu-msft · Accepted solution]

Hi @threw001 

Based on the solution @lbendlin  provided, You can try the following code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Me\OneDrive -Me\Documents\SampleDataSetMultipleIDs.xlsx"), null, true),
 Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Form ID", type text}, {"Client ID", Int64.Type}, {"Question Name", type text}, {"Question Answer", type text}, {"Form Submitted Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Question Name] <> "Country")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Question Name"]), "Question Name", "Question Answer")
in
    #"Pivoted Column"

If the solution helped, please consider to mark this way and the way  @lbendlin  offered as a solution.

Best Regards!

Yolo Zhu

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

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZHBCoJAEIZfRfYsOLsmdDUP1SUCIwPxMNRmS+LGtEK+fVsShMruaf6F7x/2Y8qSpQCchYwnCdixQUVBphvdkX2tGjzf7YRlBHEkQCxYFY4rGVJw6B/SxlxesPXxuUHzgY/bzLtad62h3qa0exrCRqGvsid5VS8bTlzMsmJg+US2uKnvv4A7Kn+yBda69fE/2V1eeFfPy7oqY9kJGw+smMiuSfa+xtxhXfzosM7VXtfqDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form ID" = _t, #"Client ID" = _t, #"Question Name" = _t, #"Question Answer" = _t, #"Form Submitted Date" = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Question Name"]), "Question Name", "Question Answer")
in
    #"Pivoted Column"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[threw001]

This is amazing thank you so much!

Sorry for my low level of knowledge.. the source I will be using is an excel file in my one drive..

When I try to replace 

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZHBCoJAEIZfRfYsOLsmdDUP1SUCIwPxMNRmS+LGtEK+fVsShMruaf6F7x/2Y8qSpQCchYwnCdixQUVBphvdkX2tGjzf7YRlBHEkQCxYFY4rGVJw6B/SxlxesPXxuUHzgY/bzLtad62h3qa0exrCRqGvsid5VS8bTlzMsmJg+US2uKnvv4A7Kn+yBda69fE/2V1eeFfPy7oqY9kJGw+smMiuSfa+xtxhXfzosM7VXtfqDQ==", BinaryEncoding.Base64), Compression.Deflate))

With the below it doesnt work 😞

Source = Excel.Workbook(File.Contents("C:\Users\Me\OneDrive -Me\Documents\SampleDataSetMultipleIDs.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

Below is the full code I am using and it is giving me - Syntax error, token identifer expected

let
    Source = Excel.Workbook(File.Contents("C:\Users\Me\OneDrive - Me\Documents\SampleDataSetMultipleIDs.xlsx"), null, true),
    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form ID" = _t, #"Client ID" = _t, #"Question Name" = _t, #"Question Answer" = _t, #"Form Submitted Date" = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Question Name"]), "Question Name", "Question Answer")
in
    #"Pivoted Column"

Are you able to provide an example code where you a directly linking from an excel document please?

Thank you so much

[lbendlin · Accepted solution]

let
   Source = Excel.Workbook(File.Contents("C:\Users\Me\OneDrive -Me\Documents\SampleDataSetMultipleIDs.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Pivoted Column" = Table.Pivot(Sheet1_Sheet, List.Distinct(Source[#"Question Name"]), "Question Name", "Question Answer")
in
    #"Pivoted Column"

This is still missing the Promoted Headers stage but you get the point.  Power Query steps usually reference a prior step.

[v-xinruzhu-msft · Accepted solution]

Hi @threw001 

Based on the solution @lbendlin  provided, You can try the following code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Me\OneDrive -Me\Documents\SampleDataSetMultipleIDs.xlsx"), null, true),
 Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Form ID", type text}, {"Client ID", Int64.Type}, {"Question Name", type text}, {"Question Answer", type text}, {"Form Submitted Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Question Name] <> "Country")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Question Name"]), "Question Name", "Question Answer")
in
    #"Pivoted Column"

If the solution helped, please consider to mark this way and the way  @lbendlin  offered as a solution.

Best Regards!

Yolo Zhu

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

[threw001]

This worked perfectly guys - thank you so much!!!!

[v-xinruzhu-msft]

Hi @threw001 

You can try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZHBCoJAEIZfRfYsOLsmdDUP1SUCIwPxMNRmS+LGtEK+fVsShMruaf6F7x/2Y8qSpQCchYwnCdixQUVBphvdkX2tGjzf7YRlBHEkQCxYFY4rGVJw6B/SxlxesPXxuUHzgY/bzLtad62h3qa0exrCRqGvsid5VS8bTlzMsmJg+US2uKnvv4A7Kn+yBda69fE/2V1eeFfPy7oqY9kJGw+smMiuSfa+xtxhXfzosM7VXtfqDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Form ID" = _t, #"Client ID" = _t, #"Question Name" = _t, #"Question Answer" = _t, #"Form Submitted Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Form ID", type text}, {"Client ID", Int64.Type}, {"Question Name", type text}, {"Question Answer", type text}, {"Form Submitted Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Question Name] <> "Country")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Question Name"]), "Question Name", "Question Answer")
in
    #"Pivoted Column"

Output

Best Regards!

Yolo Zhu

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