Forum Discussion
Manipulate table column / row with same ID
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.
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.
This worked perfectly guys - thank you so much!!!!
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.