Hello, Is it possible or how do I merge rows in Power Query? My table is as below. Customer IDNameService 1Service 2Service 3Service 1 Ref Service 2 RefService 3 Ref001Customer 1TrueNullNullDO-1NullNull001Customer 1NullTrueNullNullSH-1Null Result I would like. Customer IDNameService 1Service 2Service 3Service 1 RefService 2 RefService 3 Ref001Customer 1TrueTrueNullDO-1SH-1Null Thanks in Advance.

Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Power Query - Merge Rows

Hello,

Is it possible or how do I merge rows in Power Query?

My table is as below.

Customer ID	Name	Service 1	Service 2	Service 3	Service 1 Ref	Service 2 Ref	Service 3 Ref
001	Customer 1	True	Null	Null	DO-1	Null	Null
001	Customer 1	Null	True	Null	Null	SH-1	Null

Result I would like.

Customer ID	Name	Service 1	Service 2	Service 3	Service 1 Ref	Service 2 Ref	Service 3 Ref
001	Customer 1	True	True	Null	DO-1	SH-1	Null

Thanks in Advance.

Need Help

LukeReds

3 years ago

hi, you could use this code also but i changed "Service 1 Ref " with "Service 1 Ref" (delete the blanck char at the end)

let
Source = mysource,
#"type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"Name", type text}, {"Service 1", type text}, {"Service 2", type text}, {"Service 3", type text}, {"Service 1 Ref", type text}, {"Service 2 Ref", type text}, {"Service 3 Ref", type text}}),
Val = Table.ReplaceValue(#"type","Null",null,Replacer.ReplaceValue,{"Service 1", "Service 2", "Service 3", "Service 1 Ref", "Service 2 Ref", "Service 3 Ref"}),
Group = Table.Group(Val, {"Customer ID", "Name"}, {{"altro", each Table.FillUp(Table.FillDown(_,{"Service 1","Service 2","Service 3","Service 1 Ref","Service 2 Ref","Service 3 Ref"}),{"Service 1","Service 2","Service 3","Service 1 Ref","Service 2 Ref","Service 3 Ref"}), type table}}),
TableExp = Table.ExpandTableColumn(Group, "altro", {"Service 1", "Service 2", "Service 3", "Service 1 Ref", "Service 2 Ref", "Service 3 Ref"}, {"Service 1", "Service 2", "Service 3", "Service 1 Ref", "Service 2 Ref", "Service 3 Ref"}),
end = Table.Distinct(TableExp, {"Customer ID", "Name"})
in
end

Anonymous

3 years ago

Hi Anonymous ,

It's a common issue. The video below provides the workaround.

Power Query: How to collapse Multiple Rows to a Single Row - YouTube

And for your sample data. Here's my solution based above workaround.

Sample data:

Expected result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1S8HRR0lHyS8xNBVLBqUVlmcmpCoZIbCMktjGyGoWg1DQFZIUgAWTFYH6sTrSSgQHIQLiNIE5IUSnIQghy8dc1hHJwKEfXEewB0REbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer ID", Int64.Type}, {"Name", type text}, {"Service 1", type text}, {"Service 2", type text}, {"Service 3", type text}, {"Service 1 Ref ", type text}, {"Service 2 Ref", type text}, {"Service 3 Ref", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,{"Service 1", "Service 2", "Service 3", "Service 1 Ref ", "Service 2 Ref", "Service 3 Ref"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Customer ID", "Customer ID - Copy"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Duplicated Column", {"Customer ID - Copy"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Best Regards,

Stephen Tao

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

sample1033111.pbix28 KB

5 Replies

Anonymous

Not applicable

3 years ago

Hi Anonymous ,

It's a common issue. The video below provides the workaround.

Power Query: How to collapse Multiple Rows to a Single Row - YouTube

And for your sample data. Here's my solution based above workaround.

Sample data:

Expected result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1S8HRR0lHyS8xNBVLBqUVlmcmpCoZIbCMktjGyGoWg1DQFZIUgAWTFYH6sTrSSgQHIQLiNIE5IUSnIQghy8dc1hHJwKEfXEewB0REbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer ID", Int64.Type}, {"Name", type text}, {"Service 1", type text}, {"Service 2", type text}, {"Service 3", type text}, {"Service 1 Ref ", type text}, {"Service 2 Ref", type text}, {"Service 3 Ref", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,{"Service 1", "Service 2", "Service 3", "Service 1 Ref ", "Service 2 Ref", "Service 3 Ref"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Customer ID", "Customer ID - Copy"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Duplicated Column", {"Customer ID - Copy"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Best Regards,

Stephen Tao

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

sample1033111.pbix28 KB

Anonymous
Not applicable
3 years ago
Apologies for the delayed reply, I got pulled away on something else. This worked a treat - thank you! 😄 👍

Also - a couple of asides below which may or may not help others,

1. If you have any additional 1:1 dimension fields that could have null values (I had them in my customer closed date field) - Power Query reads null values as being unique. So you will have to replace the null values with something suitable in order for Power Query to identify duplicate rows during the process recommended here.

2. After selecting all columns and right clicking on a column heading, the "remove duplicates" option did not appear for me (do not know why?) - I had to go to the Ribbon > Home > Remove Rows > Remove Duplicates

LukeReds

Helper II

3 years ago

hi, you could use this code also but i changed "Service 1 Ref " with "Service 1 Ref" (delete the blanck char at the end)

let
Source = mysource,
#"type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"Name", type text}, {"Service 1", type text}, {"Service 2", type text}, {"Service 3", type text}, {"Service 1 Ref", type text}, {"Service 2 Ref", type text}, {"Service 3 Ref", type text}}),
Val = Table.ReplaceValue(#"type","Null",null,Replacer.ReplaceValue,{"Service 1", "Service 2", "Service 3", "Service 1 Ref", "Service 2 Ref", "Service 3 Ref"}),
Group = Table.Group(Val, {"Customer ID", "Name"}, {{"altro", each Table.FillUp(Table.FillDown(_,{"Service 1","Service 2","Service 3","Service 1 Ref","Service 2 Ref","Service 3 Ref"}),{"Service 1","Service 2","Service 3","Service 1 Ref","Service 2 Ref","Service 3 Ref"}), type table}}),
TableExp = Table.ExpandTableColumn(Group, "altro", {"Service 1", "Service 2", "Service 3", "Service 1 Ref", "Service 2 Ref", "Service 3 Ref"}, {"Service 1", "Service 2", "Service 3", "Service 1 Ref", "Service 2 Ref", "Service 3 Ref"}),
end = Table.Distinct(TableExp, {"Customer ID", "Name"})
in
end

slorin

Super User

3 years ago

Group and use Text.Combine

= Table.Group(Previous_Step, {"Customer ID", "Name"},
{{"Service 1", each Text.Combine([Service 1],""), type text},
 {"Service 2", each Text.Combine([Service 2],""), type text},
 {"Service 3", each Text.Combine([Service 3],""), type text},
 {"Service 1 Ref", each Text.Combine([Service 1 Ref],""), type text},
 {"Service 2 Ref", each Text.Combine([Service 2 Ref],""), type text},
 {"Service 3 Ref", each Text.Combine([Service 3 Ref],""), type text}})

Stéphane

ronrsnfld
Super User
3 years ago
Many questions:
Are the Null's text strings or empty cells ( null )?
Is True a text string or a logical?
Will there always be two rows per Customer ID? Or might there be more or less than two?
How do you want to combine the entries if there is more than one non-null in a single column?
In general, depending on the answers to the questions, the sequence would be something like:
Group by Customer ID
Aggregation
Demote headers
Transpose
Combine the columns
Logic here depends on the original number of rows and number of non-null entries
Transpose
Promote Headers

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