Transforming Dynamic Header into Row

[ngiam]

Dear All,

Appreciate your help in advance.

I'm having challenges in the processing the following raw data from a survey

ID	NAME1	NAME2	NAME3	NAME4	Able to drive well				Able to articulate thoughts
					{{ Q1 }}	{{ Q2 }}	{{ Q3 }}	{{ Q4 }}	{{ Q1 }}	{{ Q2 }}	{{ Q3 }}	{{ Q4 }}
111	Golding	Paul	Robert	Cindy	4	4	2	3	3	3	3	5
112	Xavier	Graham	Linda	Min	5	3	2	4	4	2	2	1

Note that {{ Q1 }} = Golding, Xavier in different survey collection, {{ Q2 }} = Paul, Graham, etc.

How can I transform them into below attached?

ID	Name	Question	Value
111	Golding	Able to drive well	4
111	Paul	Able to drive well	4
111	Robert	Able to drive well	2
111	Cindy	Able to drive well	3
112	Xavier	Able to drive well	5
112	Graham	Able to drive well	3
112	Linda	Able to drive well	2
112	Min	Able to drive well	4
111	Golding	Able to articulate thoughts	3
111	Paul	Able to articulate thoughts	3
111	Robert	Able to articulate thoughts	3
111	Cindy	Able to articulate thoughts	5
112	Xavier	Able to articulate thoughts	4
112	Graham	Able to articulate thoughts	2
112	Linda	Able to articulate thoughts	2
112	Min	Able to articulate thoughts	1

[Anonymous · Accepted solution]

Hi @ngiam 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSwcDV1QqBhgq1tVCmEYJpjGCaIJjEqY3ViVYyNDQECrnn56Rk5qUDWQGJpTlAKig/KbWoBMhwzsxLqQTSJlBsBMTGaNgUahJILiKxLDO1CGRkUWJGYi6Q4QM0IRFI+2bmgdVC9BihmQnChkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, NAME1 = _t, NAME2 = _t, NAME3 = _t, NAME4 = _t, #"Able to drive well" = _t, #" " = _t, #" .1" = _t, #" .2" = _t, #"Able to articulate thoughts" = _t, #" .3" = _t, #" .4" = _t, #" .5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NAME1", type text}, {"NAME2", type text}, {"NAME3", type text}, {"NAME4", type text}, {"Able to drive well", type text}, {" ", type text}, {" .1", type text}, {" .2", type text}, {"Able to articulate thoughts", type text}, {" .3", type text}, {" .4", type text}, {" .5", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Removed Top Rows" = Table.Skip(#"Unpivoted Other Columns",4),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Attribute.1] = " " or [Attribute.1] = "Able to articulate thoughts" or [Attribute.1] = "Able to drive well") and ([ID] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Attribute.1]=" " then List.Max(Table.SelectRows(#"Added Index",(x)=>x[ID]=[ID] and x[Index]=
List.Max(Table.SelectRows(#"Added Index",(x)=>x[ID]=[ID] and x[Index]<[Index] and x[Attribute.1]<>" ")[Index]))[Attribute.1]) else [Attribute.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Attribute.1", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"ID", "Custom", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"ID", "Custom"}, {{"Count", each _, type table [ID=nullable number, Custom=text, Value=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns2", "Custom.1", {"Value", "Index"}, {"Value", "Index"}),
    Custom1 = Table.SelectRows(#"Removed Columns", each ([ID] <> null) and ([Attribute.1] <> " " and [Attribute.1] <> "Able to articulate thoughts" and [Attribute.1] <> "Able to drive well")),
    #"Split Column by Position" = Table.SplitColumn(Custom1, "Attribute.1", Splitter.SplitTextByPositions({0, 4}, false), {"Attribute.1.1", "Attribute.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1.1", type text}, {"Attribute.1.2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom.1", {"ID", "Index"},#"Changed Type2", {"ID", "Attribute.1.2"}, "Expanded Custom.1", JoinKind.LeftOuter),
    #"Expanded Expanded Custom.1" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Custom.1", {"Value"}, {"Value.1"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Expanded Custom.1",{"Index"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns3",{"ID", "Value.1", "Custom", "Value"})
in
    #"Reordered Columns1"

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.

[ngiam]

A very silly question, I tried to change this to my source and I have this error:
Expression.Error: The column 'ID' of the table wasn't found.
Details:
ID

What are the adjustment that I should do from my end to correct this?

[Anonymous]

Hi @ngiam 

Just Replace the id column to your own column name.

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.

[DallasBaba]

@ngiam  try the following: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/BCsIwDIZfpfS8S7f5AENFBCfqSRg7ZK5shbpC7SYy9u4mWq3sJCT8X5vkb1MUfLviEd9n+Vp4jb0mXlPUrNKSOcNqqwbJ7lJrvPzGpwrWqUuvweGpNX3TultoK6OC/w5hjCM7CjZNHuOAScA04H+99JAQtM3G6Fp1DdIBevrxyVTSOoSl6uoHauqTdk5mufBOVDvDoKQlSwstXBF26ACouepeve+ZeOZJKXhZPgE=", BinaryEncoding.Base64), Compression.Deflate)),

// Define a helper function to transform rows
TransformRow = (row, startIdx, endIdx) =>
{row{0}, row{startIdx}, row{5}, row{4 + startIdx}},

// Extract rows and transform them
ExtractedRows = List.RemoveFirstN(Table.ToRows(Source), 2),
TransformedRows1 = List.TransformMany(ExtractedRows, each {1..4}, (row, idx) => TransformRow(row, idx, idx)),
TransformedRows2 = List.TransformMany(ExtractedRows, each {1..4}, (row, idx) => TransformRow(row, idx, idx + 4)),

// Combine the transformed rows
CombinedRows = TransformedRows1 & TransformedRows2,

// Create a table from the combined rows
Result = Table.FromRows(CombinedRows)
in
Result

[slorin]

Please paste your code 

don't promote headers before

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/BCsIwDIZfpfS8S7f5AENFBCfqSRg7ZK5shbpC7SYy9u4mWq3sJCT8X5vkb1MUfLviEd9n+Vp4jb0mXlPUrNKSOcNqqwbJ7lJrvPzGpwrWqUuvweGpNX3TultoK6OC/w5hjCM7CjZNHuOAScA04H+99JAQtM3G6Fp1DdIBevrxyVTSOoSl6uoHauqTdk5mufBOVDvDoKQlSwstXBF26ACouepeve+ZeOZJKXhZPgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colonne 1" = _t, #"Colonne 2" = _t, #"Colonne 3" = _t, #"Colonne 4" = _t, #"Colonne 5" = _t, #"Colonne 6" = _t, #"Colonne 7" = _t, #"Colonne 8" = _t, #"Colonne 9" = _t, #"Colonne 10" = _t, #"Colonne 11" = _t, #"Colonne 12" = _t, #"Colonne 13" = _t]),
ToRows = Table.ToRows(Source),
Result = Table.FromRows(
   List.TransformMany(List.RemoveFirstN(ToRows,2),each {1..4},(x,y) => {x{0},x{y},ToRows{0}{5},x{4+y}} )
   &
   List.TransformMany(List.RemoveFirstN(ToRows,2),each {1..4},(x,y) => {x{0},x{y},ToRows{0}{9},x{8+y}} )
   )
in
Result

Stéphane 

[slorin]

Hi

let
   Source = YourSource,
   ToRows = Table.ToRows(Source),
   Result = Table.FromRows(
      List.TransformMany(List.RemoveFirstN(ToRows,2), each {1..4}, (x,y) => {x{0}, x{y}, ToRows{0}{5}, x{4+y}} )
      &
      List.TransformMany(List.RemoveFirstN(ToRows,2), each {1..4}, (x,y) => {x{0}, x{y}, ToRows{0}{9}, x{8+y}} )
      )
in
   Result

Stéphane 

[ngiam]

Hi @slorin ,

Big thank you for responding.

After pasting into advance editor (and replacing with my source), I have this error "This table is empty."

What should I do different?