Consolidate data rows using 3 conditions - newbie to power query

newuser17

New Member
Joined
Sep 12, 2018
Messages
13
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)

54168a2e4f6b5c15d26eb73718a79238-full.png


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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Could post a sample of your data along with expected results

this is will be my source data. i have more than 40 columns of data, but i have posted here only few which are related to the above 3 validations.

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
CodeSKUCategoryDescrptionSizeStockMSRPSP
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressL
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressL
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressM
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressM
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressXS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressXS
1​
130
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressXS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressXS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
OB581283OB581283-BlackComboABCOxford Embroidered Mini DressXS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressL
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressM
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressS
1​
126
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressXS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressXS
1​
128​
34
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
OB581283OB581283-LilacComboABCOxford Embroidered Mini DressXS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressL
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressL
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressM
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressM
1​
128​
37
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressM
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]28[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressM
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]29[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressM
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]30[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]31[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]32[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressS
1​
128​
35​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]33[/COLOR]​
OB581283OB581283-NeutralComboABCOxford Embroidered Mini DressXS
1​
128​
35​

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

and this will be my desired output.

Excel 2016 (Windows) 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]O[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]P[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
SKUCategoryDescrptionSizeStockMSRPSP
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
OB581283ABCOxford Embroidered Mini Dress
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
OB581283-BlackComboABCOxford Embroidered Mini DressL
2​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
OB581283-BlackComboABCOxford Embroidered Mini DressM
2​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
OB581283-BlackComboABCOxford Embroidered Mini DressS
1​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
OB581283-BlackComboABCOxford Embroidered Mini DressXS
5​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
OB581283-LilacComboABCOxford Embroidered Mini DressL
1​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
OB581283-LilacComboABCOxford Embroidered Mini DressM
1​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
OB581283-LilacComboABCOxford Embroidered Mini DressS
4​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
OB581283-LilacComboABCOxford Embroidered Mini DressXS
3​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
OB581283-NeutralComboABCOxford Embroidered Mini DressL
2​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
OB581283-NeutralComboABCOxford Embroidered Mini DressM
5​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
OB581283-NeutralComboABCOxford Embroidered Mini DressS
3​
130​
37​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
OB581283-NeutralComboABCOxford Embroidered Mini DressXS
1​
130​
37​

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>
 
Upvote 0
as far as I can see you would need to make use of the Group By, read: https://www.powerquery.training/portfolio/group-summarize-data/

thank you for the link. it is really an awesome article.

yes i agree, i need to use the group by.. but there is an additional step that is new row to added in top of the main grouping and copied part of the relevant date. you can see it in the first row of the desired output table.

the main groping is based on Code
sub groping based on the SKU
Sub Sub grouping by Size

so the parent row contains the part of data that are common to its child group. what i am trying to achieve here is group my all the child data and create a parent row in top of the each group. so its not just about grouping only. also i need to take the maximum of MSRP and SP if there is different figures.

i hope you understand my requirement. your further advise/ help much appreciated.
 
Upvote 0
your result table is a little "weird", how you got 130 in whole column MSRP and 37 in whole column SP?
 
Upvote 0
your result table is a little "weird", how you got 130 in whole column MSRP and 37 in whole column SP?

yeah that is the case, i need find the maximum of those two rows and replace with lower value. for eg: for the same Code if there are two many data which is different from one to another we need take the maximum of the value. like 128 and 130 we need take the maximum, same applies to the SP also.
 
Upvote 0
try duplicate source table twice then
for the first duplicate:
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Code", "SKU", "Category", "Descrption", "Size"}, {{"Stock", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]
for the second duplicate:
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Code"}, {{"MSRP", each List.Max([MSRP]), type number}, {"SP", each List.Max([SP]), type number}})
in
    #"Grouped Rows"[/SIZE]
then:
Code:
[SIZE=1]let
    Source = Table.NestedJoin(#"Table1 (2)",{"Code"},#"Table1 (3)",{"Code"},"Table1 (3)",JoinKind.LeftOuter),
    #"Expanded Table1 (3)" = Table.ExpandTableColumn(Source, "Table1 (3)", {"MSRP", "SP"}, {"MSRP", "SP"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table1 (3)",{"Code"})
in
    #"Removed Columns"[/SIZE]

with the result:

SKUCategoryDescrptionSizeStockMSRPSP
OB581283-BlackComboABCOxford Embroidered Mini DressL
2​
130​
37​
OB581283-BlackComboABCOxford Embroidered Mini DressM
2​
130​
37​
OB581283-BlackComboABCOxford Embroidered Mini DressS
1​
130​
37​
OB581283-BlackComboABCOxford Embroidered Mini DressXS
5​
130​
37​
OB581283-LilacComboABCOxford Embroidered Mini DressL
1​
130​
37​
OB581283-LilacComboABCOxford Embroidered Mini DressM
1​
130​
37​
OB581283-LilacComboABCOxford Embroidered Mini DressS
4​
130​
37​
OB581283-LilacComboABCOxford Embroidered Mini DressXS
3​
130​
37​
OB581283-NeutralComboABCOxford Embroidered Mini DressL
2​
130​
37​
OB581283-NeutralComboABCOxford Embroidered Mini DressM
5​
130​
37​
OB581283-NeutralComboABCOxford Embroidered Mini DressS
3​
130​
37​
OB581283-NeutralComboABCOxford Embroidered Mini DressXS
1​
130​
37​
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top