Forum Discussion
Solved
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 f...
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.
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.