Forum Discussion

Manara's avatar
Manara
New Member
2 years ago
Solved

Transforming and transposing column values to column headers

Hello, 

I would love to transform data from this form, 

QuestionNumberText
Q1(text response only) one point one
Q2 two point two
Q33.3 
Q44.4

 

Q1 one point two
Q2 two point three
Q33.4 
Q44.5 
   

 

to this form, 

 

Q1Q2Q3Q4
one point onetwo point two3.34.4
one point twotwo point three3.4

4.5

 

I have tried using the transform function but it spits out this output, 

Q1Q2Q3Q4Q1Q2Q3Q4
nullnull3.34.4nullnull3.44.5
one point onetwo point twonullnullone point twotwo point threenullnull

 

The challenge is this data is coming from an automatic connection from a survey tool straight to powerbi in the form shown in the first table above. It spits text responses into a different column for example which makes it difficult to transform and get the desired second table above. It would be a different story if I was working with excel at some point but I would love to maintain the automatic flow without jumping out to excel.

Any help would be appreciated. This data is a small sample to show the nature of the data I am dealing with.

 

Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi Manara 

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjTUKEmtKFEoSi0uyM8rTlXIz8up1FTSUVIA4vy8VIWC/My8EqBoqlKsDlC5EVSqpDwfKgVkQaSMgcLGesZgBWABEyDTRA9EgvkgGspUQGIHGmLYBjcSi20ZRampyPaZoNtnChGIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Question = _t, Number = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Question", type text}, {"Number", type number}, {"Text", type text}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Changed Type", {{"Question", each Text.Start(_, 2), type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Extracted First Characters", "Merged", each Text.Combine({Text.From([Number], "en-PH"), [Text]}, ""), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Number", "Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Merged] <> null and [Merged] <> ""),
    Pivoted = Table.Pivot(
    #"Filtered Rows",
   List.Distinct(#"Filtered Rows"[Question]),
    "Question",
    "Merged",
    List.Distinct
),
    #"Added Custom1" = Table.AddColumn(Pivoted, "Table", each Table.FromColumns(Record.FieldValues(_), Record.FieldNames(_))),
    Table1 = #"Added Custom1"{0}[Table]
in
    Table1

     

2 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi Manara 

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjTUKEmtKFEoSi0uyM8rTlXIz8up1FTSUVIA4vy8VIWC/My8EqBoqlKsDlC5EVSqpDwfKgVkQaSMgcLGesZgBWABEyDTRA9EgvkgGspUQGIHGmLYBjcSi20ZRampyPaZoNtnChGIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Question = _t, Number = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Question", type text}, {"Number", type number}, {"Text", type text}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Changed Type", {{"Question", each Text.Start(_, 2), type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Extracted First Characters", "Merged", each Text.Combine({Text.From([Number], "en-PH"), [Text]}, ""), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Number", "Text"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Merged] <> null and [Merged] <> ""),
    Pivoted = Table.Pivot(
    #"Filtered Rows",
   List.Distinct(#"Filtered Rows"[Question]),
    "Question",
    "Merged",
    List.Distinct
),
    #"Added Custom1" = Table.AddColumn(Pivoted, "Table", each Table.FromColumns(Record.FieldValues(_), Record.FieldNames(_))),
    Table1 = #"Added Custom1"{0}[Table]
in
    Table1