Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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) ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 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

     

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

 

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

ColumnListInput={SKU_Column,SKU_Description,UNITS_PER_CASE,UNIT_L,UNIT_W,UNIT_H,UNIT_WEIGHT,MASTER_CASE_L,MASTER_CASE_W,MASTER_CASE_H,MASTER_CASE_WEIGHT,CATEGORY,SUBCATEGORY,Additional_Column1,Additional_Column2,Additional_Column3,Additional_Column4,Additional_Column5,Additional_Column6,Additional_Column7,Additional_Column8,Additional_Column9,Additional_Column10},

ColumnListToBe={"SKU","SKU Description","UNITS PER CASE","UNIT L","UNIT W","UNIT H","UNIT WEIGHT","MASTER CASE L","MASTER CASE W","MASTER CASE H","MASTER CASE WEIGHT","CATEGORY","SUBCATEGORY"},

ColumnsCombine=ColumnListToBe&List.Skip(ColumnListInput,List.Count(ColumnListToBe)),

ZipLists=List.Select(List.Zip({ColumnListInput,ColumnsCombine}),each _{0}<>null),
#"RenameColumns" = Table.RenameColumns(ITEM_INPUT,ZipLists),
#"SelectColumns" = Table.SelectColumns(#"RenameColumns", ColumnsCombine,MissingField.UseNull)

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's avatar
Anonymous
Not applicable
3 years ago

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.