Forum Discussion
Tooba_kazmi
Helper I
Helper IHow to merge rows with same headers having nulls, same as well as different datasets in rows?
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 | [email protected] | Landlord | $1200 | 28-Jul |
| null | 28-Jul | Quote | Complete | PB4 | null | [email protected] | Home | $1000 | 26-Aug |
| 24-Jul | null | null | null | PB4 | [email protected] | [email protected] | Home | null | null |
| 24-Jul | null | null | null | PB4 | [email protected] | [email protected] | 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 | [email protected] | [email protected] | Home | $1000 | 26-Aug |
| 24-Jul | 28-Jul | Quote | Complete | PB4 | [email protected] | [email protected] | Landlord | $1200 | 28-Jul |
How can i get the desired result?
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
6 Replies
- amitchandak
Super User
Tooba_kazmi , You can use group by with min/max and count options
- 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.
Tooba_kazmiData 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 [email protected] Landlord $1200 28-Jul null 28-Jul Quote Complete PB4 null [email protected] Home $1000 26-Aug 24-Jul null null null PB4 [email protected] [email protected] Home null null 24-Jul null null null PB4 [email protected] [email protected] 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 [email protected] [email protected] Home $1000 26-Aug 24-Jul 28-Jul Quote Complete PB4 [email protected] [email protected] Landlord $1200 28-Jul 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
Can you please explain this in a step by step process to make it clear for me?
