Forum Discussion
Check if value exists, if not create row with multiple values in multiple columns
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.
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"
1 Reply
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"