Forum Discussion
Power Query Merge Rows
Hi AlexisKMX ,
Sorry for late back. Based my understanding, I modify the sample and the code, please refer to it:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lHy9wYSIAYEGSrF6gBljGAyCEmojDGSDFQSKmMCZDpjM8wEp2EgGT93bIZhcZoRmgxCEosMVNII4R1nbIYZ4zQMi9OAMrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QuestionNo = _t, Value = _t, Status = _t, Rank = _t, Finding = _t, #"Input ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"QuestionNo", type text}, {"Value", type text}, {"Status", Int64.Type}, {"Rank", Int64.Type}, {"Finding", Int64.Type}}), cols=Table.ColumnNames(Source), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",1,each [Value], Replacer.ReplaceValue,cols), #"Grouped Rows" = Table.Group(#"Replaced Value", {"QuestionNo","Input ID"}, {{"Group", each Table.FillUp(_,cols)}}), #"Expanded Group" = Table.ExpandTableColumn(#"Grouped Rows", "Group", {"Value", "Status", "Rank", "Finding"}, {"Group.Value", "Group.Status", "Group.Rank", "Group.Finding"}), #"Group Rows2" = Table.Group(#"Expanded Group", {"QuestionNo","Input ID"}, {{"Group", each Table.FirstN(_,1)}}), #"Expanded Group1" = Table.ExpandTableColumn(#"Group Rows2", "Group", {"Group.Value", "Group.Status", "Group.Rank", "Group.Finding"}, {"Group.Group.Value", "Group.Group.Status", "Group.Group.Rank", "Group.Group.Finding"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Group1",{"Group.Group.Value"}) in #"Removed Columns"I only modify the group step, add the Input ID column in the Table.Group function.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi AlexisKMX ,
According to your description, I create a sample.
Here's my solution:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lHy9wYSIAYIxeoARY1gojAJsKgxkihYAixqAmQ6oxtggtUAkKifO7IBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QuestionNo = _t, Value = _t, Status = _t, Rank = _t, Finding = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"QuestionNo", type text}, {"Value", type text}, {"Status", Int64.Type}, {"Rank", Int64.Type}, {"Finding", Int64.Type}}),
cols=Table.ColumnNames(Source),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",1,each [Value], Replacer.ReplaceValue,cols),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"QuestionNo"}, {{"Group", each Table.FillUp(_,cols)}}),
#"Expanded Group" = Table.ExpandTableColumn(#"Grouped Rows", "Group", {"Value", "Status", "Rank", "Finding"}, {"Group.Value", "Group.Status", "Group.Rank", "Group.Finding"}),
#"Group Rows2" = Table.Group(#"Expanded Group", {"QuestionNo"}, {{"Group", each Table.FirstN(_,1)}}),
#"Expanded Group1" = Table.ExpandTableColumn(#"Group Rows2", "Group", {"Group.Value", "Group.Status", "Group.Rank", "Group.Finding"}, {"Group.Group.Value", "Group.Group.Status", "Group.Group.Rank", "Group.Group.Finding"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Group1",{"Group.Group.Value"})
in
#"Removed Columns"Get the result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot for the answer Kalyj
I'm just struggling with the fact that i have other columns in my main table, and when i declared the variable cols, it takes all columns but i want to take only the ones that i want to group. Is it possible to grab only specific columns in my table? and then properly declare cols with specific columns of my table?
My problem is that I have a column named Input ID, from 1 to 56, each Input ID has its own 23 Questions from Q1 to Q23. How can i group first the Input ID and then group the QuestionNo to then fill up the rows?
Here is a picture of reference.
Hi AlexisKMX ,
Sorry for late back. Based my understanding, I modify the sample and the code, please refer to it:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lHy9wYSIAYEGSrF6gBljGAyCEmojDGSDFQSKmMCZDpjM8wEp2EgGT93bIZhcZoRmgxCEosMVNII4R1nbIYZ4zQMi9OAMrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QuestionNo = _t, Value = _t, Status = _t, Rank = _t, Finding = _t, #"Input ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"QuestionNo", type text}, {"Value", type text}, {"Status", Int64.Type}, {"Rank", Int64.Type}, {"Finding", Int64.Type}}), cols=Table.ColumnNames(Source), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",1,each [Value], Replacer.ReplaceValue,cols), #"Grouped Rows" = Table.Group(#"Replaced Value", {"QuestionNo","Input ID"}, {{"Group", each Table.FillUp(_,cols)}}), #"Expanded Group" = Table.ExpandTableColumn(#"Grouped Rows", "Group", {"Value", "Status", "Rank", "Finding"}, {"Group.Value", "Group.Status", "Group.Rank", "Group.Finding"}), #"Group Rows2" = Table.Group(#"Expanded Group", {"QuestionNo","Input ID"}, {{"Group", each Table.FirstN(_,1)}}), #"Expanded Group1" = Table.ExpandTableColumn(#"Group Rows2", "Group", {"Group.Value", "Group.Status", "Group.Rank", "Group.Finding"}, {"Group.Group.Value", "Group.Group.Status", "Group.Group.Rank", "Group.Group.Finding"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Group1",{"Group.Group.Value"}) in #"Removed Columns"I only modify the group step, add the Input ID column in the Table.Group function.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the support! Exactly what i need.
