Frequent Visitor

3 years ago

Solved

Power Query Merge Rows

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!

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.

5 Replies

Replies have been turned off for this discussion

v-yanjiang-msft

Community Support

3 years ago

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.

Power Query Merge Rows.pbix19 KB

AlexisKMX
Frequent Visitor
3 years ago
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?

AlexisKMX

Frequent Visitor

3 years ago

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

Community Support

3 years ago

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.

Forum Discussion

Power Query Merge Rows

5 Replies

Related content

MERGE QUERIES

merging direct query queries

Power Query - Merge

Merging Queries in Excel Power Query

Merging Queries