Consolidate data rows using 3 conditions - newbie to power query

[newuser17]

Dear Gurus,

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.. thanks in advance for your kind help..

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

 

[horseyride]

Answered on another excel forum

https://www.mrexcel.com/board/threa...nditions-newbie-to-power-query.1070362/page-2

 

[UK_GER]

Hi @All,


even the question is answered somewhere else.....




i post a solution for the community.




let


Quelle = Excel.Workbook(File.Contents("C:\TESTDATA\Forum question.xlsx"), null, true),
Sheet1_Sheet = Quelle{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Geänderter Typ" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}}),
#"Höher gestufte Header" = Table.PromoteHeaders(#"Geänderter Typ", [PromoteAllScalars=true]),
#"Höher gestufte Header1" = Table.PromoteHeaders(#"Höher gestufte Header", [PromoteAllScalars=true]),
#"Höher gestufte Header2" = Table.PromoteHeaders(#"Höher gestufte Header1", [PromoteAllScalars=true]),
YourData = Table.SelectRows(#"Höher gestufte Header2", each

 <> null),


    MAX_MSRP = List.Max (YourData[MSRP]),
    MAX_SP = List.Max (YourData[SP]),


    YourData_Input = Table.RemoveColumns(YourData,{"SP_7", "MSRP_6", "Stock_5", "Size_4", "Descrption_3", "Category_2", "SKU_1", "Column11", "Column10", "Column9"}),
    #"add MSRP_MAX" = Table.AddColumn(YourData_Input, "M_MSRP", each MAX_MSRP),
    #"add SP_MAX" = Table.AddColumn(#"add MSRP_MAX", "M_SP", each MAX_SP),
    #"del org MSRP and SP" = Table.RemoveColumns(#"add SP_MAX",{"MSRP", "SP"}),
    Part_1 = Table.Group(#"del org MSRP and SP", {"Code", "SKU", "Category", "Descrption", "Size"}, {{"Stock", each Table.RowCount(_), type number}, {"MAX_MSRP", each List.Max([M_MSRP]), type number}, {"MAX_SP", each List.Max([M_SP]), type number}}),
    #"group Part_1 to get top row" = Table.Group(Part_1, {"Code", "MAX_MSRP", "MAX_SP"}, {{"Anzahl", each Table.RowCount(_), type number}}),
    Part_2 = Table.RemoveColumns(#"group Part_1 to get top row",{"Anzahl"}),
    #"combine Part_2 and Part_1" = Table.Combine({Part_2, Part_1}),
    #"sort a bit" = Table.ReorderColumns(#"combine Part_2 and Part_1",{"Code", "SKU", "Category", "Descrption", "Size", "Stock", "MAX_MSRP", "MAX_SP"})
in
    #"sort a bit"


Uwe