Forum Discussion

threw001's avatar
threw001
Helper III
2 years ago
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...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    lbendlin
    2 years ago
    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.

threw001's avatar
threw001
Helper III
2 years ago

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's avatar
lbendlin
Super User
2 years ago
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.