Power Query - Merge Rows

[Anonymous]

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.

[LukeReds · Accepted solution]

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 · Accepted solution]

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.           

[Anonymous]

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 · Accepted solution]

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

[ronrsnfld]

Many questions:

1. Are the Null's text strings or empty cells ( null )?
2. Is True a text string or a logical?
3. Will there always be two rows per Customer ID?  Or might there be more or less than two?
4. 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

[slorin]

Hi

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