Consolidate data rows using 3 conditions - newbie to power query

[newuser17]

Dear Experts,

i have 30000 rows of data which consist of around 40 columns. the rows have duplicate data. what i require is consolidate these duplicate data using 3 conditions and insert a parent data row containing same data as the child row. the 3 conditions to validate are:

1. Code
2. SKU
3. Size

and also there are 2 columns which have MSRP and SP, when we consolidating the data we need to take the maximum value available in both columns seperatly

i have attached a screenshot of raw data and desired outcome here with. i have ignored the columns that are not related to above 3 validation. but those columns need to be available in the out put. (in the screenshot please refer category and description)

Is this doable in power query ? because this raw data process is going to be recurring very often.... or even with a macro is doable. thanks in advance for your effort and time..

i am using excel 2016 and with no 365 subscription on a win 7 platform

 

[newuser17]

Thank you very much sandy for your help. i haven't tried the yet, but it seems it will work fine.

but as you can see in my desired output table there is a additional row (parent) contains part of the data which is similar to an header for each child row. i am struggling to get it done by power query..

hope you got clear idea about the point.

 

[sandy666]

You are welcome

You've chance to try with additional row with (again) duplicated source table then append to the merged table (theory)

edit
something like this one?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]SKU[/td][td=bgcolor:#70AD47]Category[/td][td=bgcolor:#70AD47]Descrption[/td][td=bgcolor:#70AD47]Size[/td][td=bgcolor:#70AD47]Stock[/td][td=bgcolor:#70AD47]MSRP[/td][td=bgcolor:#70AD47]SP[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-BlackCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-BlackCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]L[/td][td]

2​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-BlackCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-BlackCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]S[/td][td]

1​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-BlackCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]XS[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-LilacCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]L[/td][td]

1​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-LilacCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-LilacCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]S[/td][td]

4​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-LilacCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]XS[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-NeutralCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]L[/td][td]

2​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-NeutralCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283-NeutralCombo[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]S[/td][td]

3​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283-NeutralCombo[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]XS[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]
[/table]

 

[sandy666]

here is Example file

 

[newuser17]

here is Example file

thanks for your prompt reply. yeah that is the table i need. i am still very new to power query and trying to understand the duplicating table process. and i was unable to download the excel file.. can you please post it anywhere else ? if i have the sample file and have the code side by side i might be able to understand it fully.

 

[newuser17]

thanks for your prompt reply. yeah that is the table i need. i am still very new to power query and trying to understand the duplicating table process. and i was unable to download the excel file.. can you please post it anywhere else ? if i have the sample file and have the code side by side i might be able to understand it fully.

oops sorry!!! the table you posted is correct except the first cell. for the parent row, SKU column should have Code, not the SKU sorry for the confusion.

 

[sandy666]

this is shared file, try three dots on very right side of menu, there should be download option.

btw. I am lazy so I simple added next duplicate instead creating everything from the begining

edit:
so stay with Code instead of SKU, this is a child's play

 

[newuser17]

this is shared file, try three dots on very right side of menu, there should be download option.

i am having a bad luck with this, i was able to download but its in mhtml format and unable to open in excel 2016. also i hope the ondrive is blocked by internal firewall.

i am not getting this point. means have you changed the codes in the file or do you want me to figure it out ? sorry to keep you bothering.

 

[sandy666]

ok, here is the example file with correction: download


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Code[/td][td=bgcolor:#70AD47]Category[/td][td=bgcolor:#70AD47]Descrption[/td][td=bgcolor:#70AD47]Size[/td][td=bgcolor:#70AD47]Stock[/td][td=bgcolor:#70AD47]MSRP[/td][td=bgcolor:#70AD47]SP[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]L[/td][td]

2​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]S[/td][td]

1​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]XS[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]L[/td][td]

1​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]S[/td][td]

4​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]XS[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]L[/td][td]

2​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]OB581283[/td][td]ABC[/td][td]Oxford Embroidered Mini Dress[/td][td]S[/td][td]

3​

[/td][td]

130​

[/td][td]

37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]OB581283[/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Oxford Embroidered Mini Dress[/td][td=bgcolor:#E2EFDA]XS[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

130​

[/td][td=bgcolor:#E2EFDA]

37​

[/td][/tr]
[/table]

 

[newuser17]

hello there..

i know this is too late. the thing is i have got the result what i wanted. thanks for sandy to enlighten me the group and append concept. i was never thought about that. and then i have customized it according to my requirement by replacing values.

here is the sample file that i modified sandy's answer.

http://s000.tinyupload.com/index.php?file_id=88051951309658784279

thanks for sharing your knowledge with me..

 

[sandy666]

thanks for sharing your knowledge with me..

You are welcome
Glad to see you got what you want
Have a nice day