Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RickBickens
Helper I
Helper I

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) and renames the provided columns to be consistent and so that the measures all work with the column names after it is loaded into Power BI. I want to change my function so that if no inputs are given for some of the columns, they populate a column of zeros instead, but I am not sure how to accomplish this. 

The function code is here:

 

 

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

 

 

Currently when the function is run, the ouput is a table with all of the columns where inputs are provided and those without inputs are omitted. I want to change it so that all of the columns with inputs are provided, as well as columns filled with zeros if certain columns arent populated. 

 

Part of the challenge is that the # of rows of data this function will be applied to will fluctuate with each file. SKU_Column is guaranteed to be one of the columns, and contains the correct number of rows. Is it possible to use List.Generate to create a blank list for UNIT_L/UNIT_W/UNIT_H of length List.Count(SKU_Column) if I want to populate these columns with zeros if no input is provided?

 

Thanks!

1 ACCEPTED SOLUTION
RickBickens
Helper I
Helper I

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

 

View solution in original post

8 REPLIES 8
RickBickens
Helper I
Helper I

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
Super User
Super User

 

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

 

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

RickBickens_1-1664468403271.png

 

Error:

RickBickens_0-1664468364181.png

 

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.

RickBickens_1-1664475463265.png

 

RickBickens_0-1664475431969.png

 


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

 

jbwtp
Memorable Member
Memorable Member

Hi @RickBickens,

 

Do you want to do something like this?

I only used few columns form your query to demostrate the approach.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0dASSRkqxOtFAUkfJyckJSBqD+cZAlrOzM5A0UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU_Column = _t, SKU_Description = _t, UNITS_PER_CASE = _t]),
    tSource = Table.TransformColumnTypes(Source,{{"SKU_Column", Int64.Type}, {"SKU_Description", type text}, {"UNITS_PER_CASE", Int64.Type}}),
    f = (cols as list)=>
   let
      mTemplate = #table(cols, {}),
      combine = Table.Combine({mTemplate, tSource}),
      out = Table.SelectColumns(combine, cols)
   in out,
    Transform = f({"SKU_Column","SKU_Description", "UNIT_L"})
in
    Transform

 

Cheers,
John

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:

RickBickens_2-1664468860818.png


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)

RickBickens_3-1664468954323.png

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

RickBickens_4-1664469080344.png

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 @RickBickens.,

 

Could you please check if the below code does what you want?

I suggest to change to approach with passing a long list of arguments to a single table, this makes the code more readable and easier to maintain:

let
    Data =  Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0dASSRkBsDMRKsTrRYI6TkxNUyAQmDOI4OztDhUzBwrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU_Column = _t, SKU_Description = _t, UNITS_PER_CASE = _t, UNIT_L = _t, DUMMY = _t]),{{"SKU_Column", Int64.Type}, {"SKU_Description", type text}, {"UNITS_PER_CASE", Int64.Type}, {"UNIT_L", Int64.Type}, {"DUMMY", type text}}),
    Columns =  Table.TransformColumnTypes(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYOjXfOzynNzVPSAXEUnPNTUoHMkKBQV6VYHYgCl9Ti5KLMgpLMfJCqgCB/l1DnECDLzdEnGKIq1M8zJDg+wDUo3tkRKKSj5OusEODtrhAYEomhLt4HapWLvo+CRmaeJrJ1YAXhUAXhChrJuZjSHlBpD+zS4a6e7h4hMCNCFDSy0zURjogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Expected = _t, ChangeTo = _t, Force = _t]),{{"Expected", type text}, {"ChangeTo", type text}, {"Force", type logical}}),
    
    ColumnsToTakeFromDataTable = List.Select(Table.ColumnNames(Data), each List.Contains(Columns[Expected], _)),
    CleanUpDataTable = Table.SelectColumns(Data,ColumnsToTakeFromDataTable),
    MissingColumnsNeedToBeForced = List.RemoveItems(Table.SelectRows(Columns, each [Force] = true)[Expected], ColumnsToTakeFromDataTable),
    CompleteTable = List.Accumulate(MissingColumnsNeedToBeForced, CleanUpDataTable, (a, n)=> Table.AddColumn(a, n, each 0, type number)),
    RenameList = List.Transform(Table.ToRecords(Table.SelectRows(Columns, each List.Contains(Table.ColumnNames(CompleteTable), [Expected]))), each {[Expected], [ChangeTo]}),
    RenamedColumns = Table.RenameColumns(CompleteTable,RenameList)
in
    RenamedColumns

 

Cheers,

John  

Hi John @jbwtp ,

 

The end result is of what you provided is essentially what I am looking for. I swapped the "Expected" and "ChangeTo" in your example as the table created by the code you provided had the wrong column names.

The zero'd columns are definitely what I was after. I am a bit confused how to incorporate that functionality into my function in a way that still allows me to easily pick the other columns I want to take from the original data set. I understand your suggestion of using a list but, because the data set column names are always different, the format of my function originally allows it to be easily applied to the original data table without creating a list of the columns. 

The intention of the function is to be able to be applied directly to the data table and convert that to a table with consistantly named columns.

Hi @RickBickens,

 

I think you can have tweak it to use for your purpose. This is essentially the function:

 

f = (Data as table, Columns as table) =>
    let 
        ColumnsToTakeFromDataTable = List.Select(Table.ColumnNames(Data), each List.Contains(Columns[Expected], _)),
        CleanUpDataTable = Table.SelectColumns(Data,ColumnsToTakeFromDataTable),
        MissingColumnsNeedToBeForced = List.RemoveItems(Table.SelectRows(Columns, each [Force] = true)[Expected], ColumnsToTakeFromDataTable),
        CompleteTable = List.Accumulate(MissingColumnsNeedToBeForced, CleanUpDataTable, (a, n)=> Table.AddColumn(a, n, each 0, type number)),
        RenameList = List.Transform(Table.ToRecords(Table.SelectRows(Columns, each List.Contains(Table.ColumnNames(CompleteTable), [Expected]))), each {[Expected], [ChangeTo]}),
        RenamedColumns = Table.RenameColumns(CompleteTable,RenameList)
    in
        RenamedColumns,

 

 

This is how you would call it:

 

 Output = f(INPUT_DATA, TableSpecificTranslationTable)

 

 

The Columns table can be table specific, so for each input table you can have column that lists all the required fields against the desired name:

jbwtp_0-1664488660335.png

 

You can then select the desired columns (i.e. Expected T1 or Expected T2 with the rest of the table) or modify the function to select is based on the key columns in the INPUT_TABLE or additional text parameter or anything else. The key is that you provide a table-specific translation into the function and it does the rest.

 

Cheers,

John

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors