How to merge rows with same headers having nulls, same as well as different datasets in rows?

[Tooba_kazmi]

Hi, 

I need help to merge multiple rows having same headers, nulls in some of the headers of multiple rows, having same data in some of the headers and different data in the other headers just as shown below:

Data in raw format:

Lead Created Date	ACTUAL_QUOTE_DT	CONTRACT_STAGE	PROCESSING_STATUS	BRANCH_QUOTED	REFERRED_BY	CUSTOMER_EMAIL	PRODUCT	NET_PREMIUM	PROCESSING_DT
null	28-Jul	New Business	Incomplete	PB4	null	rst@email.com	Landlord	$1200	28-Jul
null	28-Jul	Quote	Complete	PB4	null	rst@email.com	Home	$1000	26-Aug
24-Jul	null	null	null	PB4	xyz@email.com	rst@email.com	Home	null	null
24-Jul	null	null	null	PB4	xyz@email.com	rst@email.com	Landlord	null	null

Desired Result:

Lead Created Date	ACTUAL_QUOTE_DT	CONTRACT_STAGE	PROCESSING_STATUS	BRANCH_QUOTED	REFERRED_BY	CUSTOMER_EMAIL	PRODUCT	NET_PREMIUM	PROCESSING_DT
24-Jul	28-Jul	New Business	Incomplete	PB4	xyz@email.com	rst@email.com	Home	$1000	26-Aug
24-Jul	28-Jul	Quote	Complete	PB4	xyz@email.com	rst@email.com	Landlord	$1200	28-Jul

How can i get the desired result?

[Tooba_kazmi]

Data in raw format:

Lead Created Date	ACTUAL_QUOTE_DT	CONTRACT_STAGE	PROCESSING_STATUS	BRANCH_QUOTED	REFERRED_BY	CUSTOMER_EMAIL	PRODUCT	NET_PREMIUM	PROCESSING_DT
null	28-Jul	New Business	Incomplete	PB4	null	rst@email.com	Landlord	$1200	28-Jul
null	28-Jul	Quote	Complete	PB4	null	rst@email.com	Home	$1000	26-Aug
24-Jul	null	null	null	PB4	xyz@email.com	rst@email.com	Home	null	null
24-Jul	null	null	null	PB4	xyz@email.com	rst@email.com	Landlord	null	null

Desired Result:

Lead Created Date	ACTUAL_QUOTE_DT	CONTRACT_STAGE	PROCESSING_STATUS	BRANCH_QUOTED	REFERRED_BY	CUSTOMER_EMAIL	PRODUCT	NET_PREMIUM	PROCESSING_DT
24-Jul	28-Jul	New Business	Incomplete	PB4	xyz@email.com	rst@email.com	Home	$1000	26-Aug
24-Jul	28-Jul	Quote	Complete	PB4	xyz@email.com	rst@email.com	Landlord	$1200	28-Jul

[v-shex-msft · Accepted solution]

Hi @Tooba_kazmi,

You can enter to the 'query editor' to use group function to group these records based on "CUSTOMER_EMAIL","PRODUCT","BRANCH_QUOTED" fields. Then you can nest the ‘fill down’ function to process these records and filter not matched records to get merged result.

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSUTKy0PUqBTH8UssVnEqLM/NSi4uBXM+85PzcgpzUklQgJ8DJBEhCdRQVlzik5iZm5ugBVQD5Pol5KTn5RSlApoqhkYEBwtBYHUxrAkvzwUY6E2m6R35uKthkA4jJZrqOpelgk41MoEZCdaJSEEMrKqtQTMNhOrJWahmNFCwoxscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Lead Created Date" = _t, ACTUAL_QUOTE_DT = _t, CONTRACT_STAGE = _t, PROCESSING_STATUS = _t, BRANCH_QUOTED = _t, REFERRED_BY = _t, CUSTOMER_EMAIL = _t, PRODUCT = _t, NET_PREMIUM = _t, PROCESSING_DT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lead Created Date", type date}, {"ACTUAL_QUOTE_DT", type date}, {"CONTRACT_STAGE", type text}, {"PROCESSING_STATUS", type text}, {"BRANCH_QUOTED", type text}, {"REFERRED_BY", type text}, {"CUSTOMER_EMAIL", type text}, {"PRODUCT", type text}, {"NET_PREMIUM", Currency.Type}, {"PROCESSING_DT", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","null",null,Replacer.ReplaceValue,{"Lead Created Date", "ACTUAL_QUOTE_DT", "CONTRACT_STAGE", "PROCESSING_STATUS", "BRANCH_QUOTED", "REFERRED_BY", "CUSTOMER_EMAIL", "PRODUCT", "NET_PREMIUM", "PROCESSING_DT"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"CUSTOMER_EMAIL","PRODUCT","BRANCH_QUOTED"}, {{"Content", each Table.Skip(Table.FillDown(_,Table.ColumnNames(_)),1), type table}}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Grouped Rows", "Content", {"Lead Created Date", "ACTUAL_QUOTE_DT", "CONTRACT_STAGE", "PROCESSING_STATUS", "REFERRED_BY", "NET_PREMIUM", "PROCESSING_DT"}, {"Lead Created Date", "ACTUAL_QUOTE_DT", "CONTRACT_STAGE", "PROCESSING_STATUS", "REFERRED_BY", "NET_PREMIUM", "PROCESSING_DT"})
in
    #"Expanded Content"

Regards,

Xiaoxin Sheng

[Tooba_kazmi]

This works perfectly fine for small data set but not with large. 

[Tooba_kazmi]

Can you please explain this in a step by step process to make it clear for me?

[Ashish_Mathur]

Hi,

Your data has not been pasted properly.  Share data in a format that can be pasted in an MS Excel file and show the expected result.

[amitchandak]

@Tooba_kazmi , You can use group by with min/max and count options

https://docs.microsoft.com/en-us/power-query/group-by