Forum Discussion
Anonymous
3 years agoNot applicable
Add column with blank/zeros in custom function if no data provided
Hi there, I am looking for some help modifying a function I am working on in Power Query. Currently the function takes a variety of inputs from the user (most optional except for the first one) ...
- Anonymous3 years ago
I was able to get the desired output with the following code. The only addition needed was the
#"RenameUnitsPC" = if UNITS_PER_CASE = null then Table.AddColumn(#"RenameSKUDesc","UNITS PER CASE", each "0") else Table.RenameColumns(#"RenameSKUDesc",{UNITS_PER_CASE,"UNITS PER CASE"}),"Table.AddColumn(#"RenameSKUDesc", "UNITS PER CASE", each "0") to the boolean expression of the columns I wanted included as only zeros if no input was added by the user.
//If no inputs are provided for UNITS PER CASE, UNIT L, UNIT W, UNIT H, or UNIT WEIGHT, these columns will be included in the resulting table as columns of Zeros let //declare a function ImportAndRename = (SKU_Column as text, optional SKU_Description as text, optional UNITS_PER_CASE as text, optional UNIT_L as text, optional UNIT_W as text, optional UNIT_H as text, optional UNIT_WEIGHT as text, optional MASTER_CASE_L as text, optional MASTER_CASE_W as text, optional MASTER_CASE_H as text, optional MASTER_CASE_WEIGHT as text, optional CATEGORY as text, optional SUBCATEGORY as text, optional Additional_Column1 as text, optional Additional_Column2 as text, optional Additional_Column3 as text, optional Additional_Column4 as text, optional Additional_Column5 as text, optional Additional_Column6 as text, optional Additional_Column7 as text, optional Additional_Column8 as text, optional Additional_Column9 as text, optional Additional_Column10 as text) => let Optional_SKU_Description = if SKU_Description = null then {} else {SKU_Description}, Optional_UNITS_PER_CASE = if UNITS_PER_CASE = null then {} else {UNITS_PER_CASE}, Optional_UNIT_L = if UNIT_L = null then {} else {UNIT_L}, Optional_UNIT_W = if UNIT_W = null then {} else {UNIT_W}, Optional_UNIT_H = if UNIT_H = null then {} else {UNIT_H}, Optional_UNIT_WEIGHT = if UNIT_WEIGHT = null then {} else {UNIT_WEIGHT}, Optional_MASTER_CASE_L = if MASTER_CASE_L = null then {} else {MASTER_CASE_L}, Optional_MASTER_CASE_W = if MASTER_CASE_W = null then {} else {MASTER_CASE_W}, Optional_MASTER_CASE_H = if MASTER_CASE_H = null then {} else {MASTER_CASE_H}, Optional_MASTER_CASE_WEIGHT = if MASTER_CASE_WEIGHT = null then {} else {MASTER_CASE_WEIGHT}, Optional_CATEGORY = if CATEGORY = null then {} else {CATEGORY}, Optional_SUBCATEGORY = if SUBCATEGORY = null then {} else {SUBCATEGORY}, Optional_Columns1 = if Additional_Column1 = null then {} else {Additional_Column1}, Optional_Columns2 = if Additional_Column2 = null then {} else {Additional_Column2}, Optional_Columns3 = if Additional_Column3 = null then {} else {Additional_Column3}, Optional_Columns4 = if Additional_Column4 = null then {} else {Additional_Column4}, Optional_Columns5 = if Additional_Column5 = null then {} else {Additional_Column5}, Optional_Columns6 = if Additional_Column6 = null then {} else {Additional_Column6}, Optional_Columns7 = if Additional_Column7 = null then {} else {Additional_Column7}, Optional_Columns8 = if Additional_Column8 = null then {} else {Additional_Column8}, Optional_Columns9 = if Additional_Column9 = null then {} else {Additional_Column9}, Optional_Columns10 = if Additional_Column10 = null then {} else {Additional_Column10}, #"SelectColumns" = Table.SelectColumns(#"ITEM_INPUT", List.Combine({{SKU_Column}, Optional_SKU_Description, Optional_UNITS_PER_CASE, Optional_UNIT_L, Optional_UNIT_W, Optional_UNIT_H, Optional_UNIT_WEIGHT, Optional_MASTER_CASE_L, Optional_MASTER_CASE_W, Optional_MASTER_CASE_H, Optional_MASTER_CASE_WEIGHT, Optional_CATEGORY, Optional_SUBCATEGORY, Optional_Columns1, Optional_Columns2, Optional_Columns3, Optional_Columns4, Optional_Columns5, Optional_Columns6, Optional_Columns7, Optional_Columns8, Optional_Columns9, Optional_Columns10})), #"RenameColumns" = Table.RenameColumns(#"SelectColumns", {{SKU_Column, "SKU"}}), //UNITS PER CASE, UNIT L, UNIT W, UNIT H and UNIT WEIGHT are created as columns of Zeros in this step if they were not passed as inputs to the function. #"RenameSKUDesc" = if SKU_Description = null then #"RenameColumns" else Table.RenameColumns(#"RenameColumns",{SKU_Description, "SKU Description"}), #"RenameUnitsPC" = if UNITS_PER_CASE = null then Table.AddColumn(#"RenameSKUDesc","UNITS PER CASE", each "0") else Table.RenameColumns(#"RenameSKUDesc",{UNITS_PER_CASE,"UNITS PER CASE"}), #"RenameUNITL" = if UNIT_L = null then Table.AddColumn(#"RenameUnitsPC","UNIT L",each "0") else Table.RenameColumns(#"RenameUnitsPC",{UNIT_L, "UNIT L"}), #"RenameUNITW" = if UNIT_W = null then Table.AddColumn(#"RenameUNITL","UNIT W",each "0") else Table.RenameColumns(#"RenameUNITL",{UNIT_W, "UNIT W"}), #"RenameUNITH" = if UNIT_H = null then Table.AddColumn(#"RenameUNITW","UNIT H",each "0") else Table.RenameColumns(#"RenameUNITW",{UNIT_H, "UNIT H"}), #"RenameUNITWEIGHT" = if UNIT_WEIGHT = null then Table.AddColumn(#"RenameUNITH","UNIT WEIGHT",each "0") else Table.RenameColumns(#"RenameUNITH",{UNIT_WEIGHT, "UNIT WEIGHT"}), #"RenameMCL" = if MASTER_CASE_L = null then #"RenameUNITWEIGHT" else Table.RenameColumns(#"RenameUNITWEIGHT",{MASTER_CASE_L,"MASTER CASE L"}), #"RenameMCW" = if MASTER_CASE_W = null then #"RenameMCL" else Table.RenameColumns(#"RenameMCL",{MASTER_CASE_W,"MASTER CASE W"}), #"RenameMCH" = if MASTER_CASE_H = null then #"RenameMCW" else Table.RenameColumns(#"RenameMCW",{MASTER_CASE_H, "MASTER CASE H"}), #"RenameMCWeight" = if MASTER_CASE_WEIGHT = null then #"RenameMCH" else Table.RenameColumns(#"RenameMCH",{MASTER_CASE_WEIGHT, "MASTER CASE WEIGHT"}), #"RenameCat" = if CATEGORY = null then #"RenameMCWeight" else Table.RenameColumns(#"RenameMCWeight",{CATEGORY,"CATEGORY"}), #"RenameSubCat"= if SUBCATEGORY = null then #"RenameCat" else Table.RenameColumns(#"RenameCat",{SUBCATEGORY,"SUBCATEGORY"}) in #"RenameSubCat", //declare Column_Type list Column_Type = type text meta [Documentation.Description = "Please select the SKU column", Documentation.AllowedValues = Table.ColumnNames(#"ITEM_INPUT")], //declare custom function type using custom number types MyFunctionType = type function( SKU_Column as Column_Type, optional SKU_Description as Column_Type, optional UNITS_PER_CASE as Column_Type, optional UNIT_L as Column_Type, optional UNIT_W as Column_Type, optional UNIT_H as Column_Type, optional UNIT_WEIGHT as Column_Type, optional MASTER_CASE_L as Column_Type, optional MASTER_CASE_W as Column_Type, optional MASTER_CASE_H as Column_Type, optional MASTER_CASE_WEIGHT as Column_Type, optional CATEGORY as Column_Type, optional SUBCATEGORY as Column_Type, optional Additional_Column1 as Column_Type, optional Additional_Column2 as Column_Type, optional Additional_Column3 as Column_Type, optional Additional_Column4 as Column_Type, optional Additional_Column5 as Column_Type, optional Additional_Column6 as Column_Type, optional Additional_Column7 as Column_Type, optional Additional_Column8 as Column_Type, optional Additional_Column9 as Column_Type, optional Additional_Column10 as Column_Type) as table, //cast original function to be of new custom function type ImportAndRenameV2 = Value.ReplaceType( ImportAndRename, MyFunctionType) in ImportAndRenameV2
Anonymous
3 years agoNot applicable
I was able to get the desired output with the following code. The only addition needed was the
#"RenameUnitsPC" = if UNITS_PER_CASE = null then Table.AddColumn(#"RenameSKUDesc","UNITS PER CASE", each "0") else Table.RenameColumns(#"RenameSKUDesc",{UNITS_PER_CASE,"UNITS PER CASE"}),"Table.AddColumn(#"RenameSKUDesc", "UNITS PER CASE", each "0") to the boolean expression of the columns I wanted included as only zeros if no input was added by the user.
//If no inputs are provided for UNITS PER CASE, UNIT L, UNIT W, UNIT H, or UNIT WEIGHT, these columns will be included in the resulting table as columns of Zeros
let
//declare a function
ImportAndRename =
(SKU_Column as text, optional SKU_Description as text, optional UNITS_PER_CASE as text, optional UNIT_L as text, optional UNIT_W as text, optional UNIT_H as text, optional UNIT_WEIGHT as text, optional MASTER_CASE_L as text, optional MASTER_CASE_W as text, optional MASTER_CASE_H as text, optional MASTER_CASE_WEIGHT as text, optional CATEGORY as text, optional SUBCATEGORY as text, optional Additional_Column1 as text, optional Additional_Column2 as text, optional Additional_Column3 as text, optional Additional_Column4 as text, optional Additional_Column5 as text, optional Additional_Column6 as text, optional Additional_Column7 as text, optional Additional_Column8 as text, optional Additional_Column9 as text, optional Additional_Column10 as text) => let
Optional_SKU_Description = if SKU_Description = null then {} else {SKU_Description},
Optional_UNITS_PER_CASE = if UNITS_PER_CASE = null then {} else {UNITS_PER_CASE},
Optional_UNIT_L = if UNIT_L = null then {} else {UNIT_L},
Optional_UNIT_W = if UNIT_W = null then {} else {UNIT_W},
Optional_UNIT_H = if UNIT_H = null then {} else {UNIT_H},
Optional_UNIT_WEIGHT = if UNIT_WEIGHT = null then {} else {UNIT_WEIGHT},
Optional_MASTER_CASE_L = if MASTER_CASE_L = null then {} else {MASTER_CASE_L},
Optional_MASTER_CASE_W = if MASTER_CASE_W = null then {} else {MASTER_CASE_W},
Optional_MASTER_CASE_H = if MASTER_CASE_H = null then {} else {MASTER_CASE_H},
Optional_MASTER_CASE_WEIGHT = if MASTER_CASE_WEIGHT = null then {} else {MASTER_CASE_WEIGHT},
Optional_CATEGORY = if CATEGORY = null then {} else {CATEGORY},
Optional_SUBCATEGORY = if SUBCATEGORY = null then {} else {SUBCATEGORY},
Optional_Columns1 = if Additional_Column1 = null then {} else {Additional_Column1},
Optional_Columns2 = if Additional_Column2 = null then {} else {Additional_Column2},
Optional_Columns3 = if Additional_Column3 = null then {} else {Additional_Column3},
Optional_Columns4 = if Additional_Column4 = null then {} else {Additional_Column4},
Optional_Columns5 = if Additional_Column5 = null then {} else {Additional_Column5},
Optional_Columns6 = if Additional_Column6 = null then {} else {Additional_Column6},
Optional_Columns7 = if Additional_Column7 = null then {} else {Additional_Column7},
Optional_Columns8 = if Additional_Column8 = null then {} else {Additional_Column8},
Optional_Columns9 = if Additional_Column9 = null then {} else {Additional_Column9},
Optional_Columns10 = if Additional_Column10 = null then {} else {Additional_Column10},
#"SelectColumns" = Table.SelectColumns(#"ITEM_INPUT", List.Combine({{SKU_Column}, Optional_SKU_Description, Optional_UNITS_PER_CASE, Optional_UNIT_L, Optional_UNIT_W, Optional_UNIT_H, Optional_UNIT_WEIGHT, Optional_MASTER_CASE_L, Optional_MASTER_CASE_W, Optional_MASTER_CASE_H, Optional_MASTER_CASE_WEIGHT, Optional_CATEGORY, Optional_SUBCATEGORY, Optional_Columns1, Optional_Columns2, Optional_Columns3, Optional_Columns4, Optional_Columns5, Optional_Columns6, Optional_Columns7, Optional_Columns8, Optional_Columns9, Optional_Columns10})),
#"RenameColumns" = Table.RenameColumns(#"SelectColumns", {{SKU_Column, "SKU"}}),
//UNITS PER CASE, UNIT L, UNIT W, UNIT H and UNIT WEIGHT are created as columns of Zeros in this step if they were not passed as inputs to the function.
#"RenameSKUDesc" = if SKU_Description = null then #"RenameColumns" else Table.RenameColumns(#"RenameColumns",{SKU_Description, "SKU Description"}),
#"RenameUnitsPC" = if UNITS_PER_CASE = null then Table.AddColumn(#"RenameSKUDesc","UNITS PER CASE", each "0") else Table.RenameColumns(#"RenameSKUDesc",{UNITS_PER_CASE,"UNITS PER CASE"}),
#"RenameUNITL" = if UNIT_L = null then Table.AddColumn(#"RenameUnitsPC","UNIT L",each "0") else Table.RenameColumns(#"RenameUnitsPC",{UNIT_L, "UNIT L"}),
#"RenameUNITW" = if UNIT_W = null then Table.AddColumn(#"RenameUNITL","UNIT W",each "0") else Table.RenameColumns(#"RenameUNITL",{UNIT_W, "UNIT W"}),
#"RenameUNITH" = if UNIT_H = null then Table.AddColumn(#"RenameUNITW","UNIT H",each "0") else Table.RenameColumns(#"RenameUNITW",{UNIT_H, "UNIT H"}),
#"RenameUNITWEIGHT" = if UNIT_WEIGHT = null then Table.AddColumn(#"RenameUNITH","UNIT WEIGHT",each "0") else Table.RenameColumns(#"RenameUNITH",{UNIT_WEIGHT, "UNIT WEIGHT"}),
#"RenameMCL" = if MASTER_CASE_L = null then #"RenameUNITWEIGHT" else Table.RenameColumns(#"RenameUNITWEIGHT",{MASTER_CASE_L,"MASTER CASE L"}),
#"RenameMCW" = if MASTER_CASE_W = null then #"RenameMCL" else Table.RenameColumns(#"RenameMCL",{MASTER_CASE_W,"MASTER CASE W"}),
#"RenameMCH" = if MASTER_CASE_H = null then #"RenameMCW" else Table.RenameColumns(#"RenameMCW",{MASTER_CASE_H, "MASTER CASE H"}),
#"RenameMCWeight" = if MASTER_CASE_WEIGHT = null then #"RenameMCH" else Table.RenameColumns(#"RenameMCH",{MASTER_CASE_WEIGHT, "MASTER CASE WEIGHT"}),
#"RenameCat" = if CATEGORY = null then #"RenameMCWeight" else Table.RenameColumns(#"RenameMCWeight",{CATEGORY,"CATEGORY"}),
#"RenameSubCat"= if SUBCATEGORY = null then #"RenameCat" else Table.RenameColumns(#"RenameCat",{SUBCATEGORY,"SUBCATEGORY"})
in #"RenameSubCat",
//declare Column_Type list
Column_Type = type text
meta
[Documentation.Description = "Please select the SKU column",
Documentation.AllowedValues = Table.ColumnNames(#"ITEM_INPUT")],
//declare custom function type using custom number types
MyFunctionType = type function(
SKU_Column as Column_Type,
optional SKU_Description as Column_Type,
optional UNITS_PER_CASE as Column_Type,
optional UNIT_L as Column_Type,
optional UNIT_W as Column_Type,
optional UNIT_H as Column_Type,
optional UNIT_WEIGHT as Column_Type,
optional MASTER_CASE_L as Column_Type,
optional MASTER_CASE_W as Column_Type,
optional MASTER_CASE_H as Column_Type,
optional MASTER_CASE_WEIGHT as Column_Type,
optional CATEGORY as Column_Type,
optional SUBCATEGORY as Column_Type,
optional Additional_Column1 as Column_Type,
optional Additional_Column2 as Column_Type,
optional Additional_Column3 as Column_Type,
optional Additional_Column4 as Column_Type,
optional Additional_Column5 as Column_Type,
optional Additional_Column6 as Column_Type,
optional Additional_Column7 as Column_Type,
optional Additional_Column8 as Column_Type,
optional Additional_Column9 as Column_Type,
optional Additional_Column10 as Column_Type)
as table,
//cast original function to be of new custom function type
ImportAndRenameV2 = Value.ReplaceType(
ImportAndRename,
MyFunctionType)
in
ImportAndRenameV2