Add column with blank/zeros in custom function if no data provided

Hi John jbwtp,

Thanks for your reply. Yeah that seems to be in the right direction of what I am looking for.

Currently the function works by taking input parameters which are the orignal column names from the data set, and renaming them to be consistent with the measures on the Power BI Desktop side of things. Sometimes the original data set will have extra columns that aren't needed so this function is a way to clean up the columns present and change the column names. 
The function input looks like this:

And if certain information is not available in the original data set I can ignore that parameter and the column is not populated. 
For example, with the following inputs (Notice UNIT_H is blank)

I get the following table where there is no UNIT_H column. 

I am hoping to change the formula so that if UNIT_L, UNIT_W, UNIT_H, and UNIT_WEIGHT can be left as blank inputs but they are still included in the table with NULL or 0 values. These are the only columns I want to add this functionality too. 

I will post my function code again, I believe that the generation/replacement of values as 0 or null would need to go in the section of the code after the ImportAndRename line?

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"}}), 
#"RenameSKUDesc" = if SKU_Description = null then #"RenameColumns" else Table.RenameColumns(#"RenameColumns",{SKU_Description, "SKU Description"}), 
#"RenameUnitsPC" = if UNITS_PER_CASE = null then #"RenameSKUDesc" else Table.RenameColumns(#"RenameSKUDesc",{UNITS_PER_CASE,"UNITS PER CASE"}),
#"RenameUNITL" = if UNIT_L = null then #"RenameUnitsPC" else Table.RenameColumns(#"RenameUnitsPC",{UNIT_L, "UNIT L"}),
#"RenameUNITW" = if UNIT_W = null then #"RenameUNITL" else Table.RenameColumns(#"RenameUNITL",{UNIT_W, "UNIT W"}),
#"RenameUNITH"  = if UNIT_H = null then #"RenameUNITW" else Table.RenameColumns(#"RenameUNITW",{UNIT_H, "UNIT H"}), 
#"RenameUNITWEIGHT" = if UNIT_WEIGHT = null then #"RenameUNITH" 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

Thanks!

Hi wdx223_Daniel, I copied the function code you provided and put the following inputs in and got the following error.
Inputs:

Error:

I also got the same error when ommitting the UNIT_L, UNIT_W, UNIT_H, and UNIT_WEIGHT inputs. I changed the reference from #"RenameSubCat" to #"SelectColumns" instead but got this error.

My hope is that when these input parameters are left as blank, a column of zeros is produced instead.