Check if value exists, if not create row with multiple values in multiple columns

[elisedebrie]

Hello all, 

I need some help with a complex Power Query that i need to set up. 

In the following query i have multiple lines for an itemnumber with different operationID's. I need to check if the value H-ACI and H-LAV exist as an operationId for all the items. If these operationId's do not exist, a new row(s) should be created for this item with the new operationId. In this new row we also need to set column processQuantity to 1 and ProcessTime to 0,01.

If the operationId already exists, nothing needs to happen. 

[elisedebrie · Accepted solution]

Hello all, 

It seems like there is no easy solution for this so i have created some workarounds and got the result that i needed. For everyone that is interested i have used the following code:

let

    Source = 

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ITEMNUMBER", type text}, {"OPERATIONID", type text}, {"PROCESSQUANTITY", Int64.Type}, {"QUEUETIMEAFTER", Int64.Type}, {"QUEUETIMEBEFORE", Int64.Type}, {"ROUTETYPE", type text}, {"SETUPTIME", Int64.Type}, {"TRANSITTIME", Int64.Type}, {"RESOURCEQUANTITY", Int64.Type}, {"ROUTEGROUPID", type text}, {"PROCESSCOSTCATEGORYID", type text}, {"SETUPCOSTCATEGORYID", type text}, {"ROUTEID", type text}, {"CONSUMPTIONCALCULATIONFACTOR", Int64.Type}, {"CONSUMPTIONCALCULATIONFORMULA", type text}, {"QUANTITYCOSTCATEGORYID", type text}, {"Processtime", type number}, {"LOADPERCENTAGE", type number}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"ITEMNUMBER", "OPERATIONID"}, {{"Count", each _, type table [ITEMNUMBER=nullable text, OPERATIONID=nullable text, PROCESSQUANTITY=nullable number, QUEUETIMEAFTER=nullable number, QUEUETIMEBEFORE=nullable number, ROUTETYPE=nullable text, SETUPTIME=nullable number, TRANSITTIME=nullable number, RESOURCEQUANTITY=nullable number, ROUTEGROUPID=nullable text, PROCESSCOSTCATEGORYID=nullable text, SETUPCOSTCATEGORYID=nullable text, ROUTEID=nullable text, CONSUMPTIONCALCULATIONFACTOR=nullable number, CONSUMPTIONCALCULATIONFORMULA=nullable text, QUANTITYCOSTCATEGORYID=nullable text, Processtime=nullable number, LOADPERCENTAGE=nullable number]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if ([OPERATIONID] = "H-FER") then List.Repeat({""},6)

else null),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"ITEMNUMBER", "OPERATIONID"}, {{"Count", each _, type table [ITEMNUMBER=nullable text, OPERATIONID=nullable text, Count=table, Custom=nullable text]}}),

    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom index", each Table.AddIndexColumn([Count],"Index",1)),

    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom index"}),

    #"Expanded Custom index" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom index", {"ITEMNUMBER", "OPERATIONID", "Count", "Custom", "Index"}, {"ITEMNUMBER", "OPERATIONID", "Count", "Custom", "Index"}),

    #"Added Custom2" = Table.AddColumn(#"Expanded Custom index", "OperationId New", each if [Index] = 2 then "H-ACI" else if [Index] = 3 then "H-LAV" else if [Index] = 4 then "H-LEV" else if [Index] = 5 then "H-LIS" else if [Index] = 6 then "H-POL" else [OPERATIONID]),

    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom2", "Count", {"PROCESSQUANTITY", "QUEUETIMEAFTER", "QUEUETIMEBEFORE", "ROUTETYPE", "SETUPTIME", "TRANSITTIME", "RESOURCEQUANTITY", "ROUTEGROUPID", "PROCESSCOSTCATEGORYID", "SETUPCOSTCATEGORYID", "ROUTEID", "CONSUMPTIONCALCULATIONFACTOR", "CONSUMPTIONCALCULATIONFORMULA", "QUANTITYCOSTCATEGORYID", "Processtime", "LOADPERCENTAGE"}, {"PROCESSQUANTITY", "QUEUETIMEAFTER", "QUEUETIMEBEFORE", "ROUTETYPE", "SETUPTIME", "TRANSITTIME", "RESOURCEQUANTITY", "ROUTEGROUPID", "PROCESSCOSTCATEGORYID", "SETUPCOSTCATEGORYID", "ROUTEID", "CONSUMPTIONCALCULATIONFACTOR", "CONSUMPTIONCALCULATIONFORMULA", "QUANTITYCOSTCATEGORYID", "Processtime", "LOADPERCENTAGE"}),

    #"Added Custom3" = Table.AddColumn(#"Expanded Count", "ProcessQuantityNew", each if [OperationId New] = "H-ACI" then 1 else if [OperationId New] = "H-LAV" then 1 else if [OperationId New] = "H-LEV" then 1 else if [OperationId New] = "H-LIS" then 1 else if [OperationId New] = "H-POL" then 1 else [PROCESSQUANTITY]),

    #"Added Conditional Column" = Table.AddColumn(#"Added Custom3", "PROCESSTIMENew", each if [OperationId New] = "H-ACI" then 0.01 else if [OperationId New] = "H-LAV" then 0.01 else if [OperationId New] = "H-LEV" then 0.01 else if [OperationId New] = "H-LIS" then 0.01 else if [OperationId New] = "H-POL" then 0.01 else [Processtime]),

    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "LOADPERCENTAGENew", each if [OperationId New] = "H-ACI" then 0.01 else if [OperationId New] = "H-LAV" then 0.01 else if [OperationId New] = "H-LEV" then 0.01 else if [OperationId New] = "H-LIS" then 0.01 else if [OperationId New] = "H-POL" then 0.01 else [LOADPERCENTAGE])

in

    #"Added Conditional Column1"