Forum Discussion
Manipulate table column / row with same ID
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 |
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.
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.
6 Replies
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.
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
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.
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.
