Power Query Merge Rows

[AlexisKMX]

Hi friends!

I was modifying this table in Power Query but i got stuck in this step. Basically what i want to do is to distribute the column [Value] in all the other columns to its right. If the column has a 1 then there should be placed the value of the column [Value].

For example, the first row, "OK" should be placed in the column [Status] because there is a 1 there.

The other thing after achieve that is to merge the duplicates values in the column [QuestionNo] in just one row, basically put it together in one row, and that the values ​​of the columns on the right are also put together in the same row. For example in the Q4, there are four Q4 since it has values in [Rank], [Finding] and [Responsible], but i want those values to be in just one single row to have only one Q4. 

Please let me know if my explanation wasn't clear at all and i will really appreciate all your advices.

Thanks people!

[v-yanjiang-msft]

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.

[AlexisKMX]

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.

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisKMX]

Thanks for the support! Exactly what i need. 

[AlexisKMX]

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?