Function for dynamically renaming Columns, some consistent, some optional

[RickBickens]

Hello,

I am modifying an existing Power Query function which takes column names from various source files and renames them consistantly so that measures that reference these columns function in Power BI. 

I have some columns that are expected in the data, and some that are optional. When those optional columns are present in the data, I would like them to be able to be included in the function and renamed just like the consistant columns. The screenshot below is the column name inputs. When "Master Case" or Catagory (or both) information is included in the source, I would like to have those processed by the function and renamed, but it seems right now I would need all of them present to successfully rename them. 

The function M code with my attempt at including optional rename is below, I think my error lies in then#"RenameColumns" section:

let
//declare a function

ImportAndRename =
(SKU_Column as text, SKU_Description as text, UNITS_PER_CASE as text, UNIT_L as text, UNIT_W as text, UNIT_H as text, 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 CATAGORY as text, optional SUBCATAGORY 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) => let

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_CATAGORY = if CATAGORY = null then {} else {CATAGORY}, 
Optional_SUBCATAGORY = if SUBCATAGORY = null then {} else {SUBCATAGORY},
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_Descript = if Description = null then {} else {Description},

#"SelectColumns" = Table.SelectColumns(ITEM_INPUT, List.Combine({{SKU_Column}, {SKU_Description}, {UNITS_PER_CASE}, {UNIT_L}, {UNIT_W}, {UNIT_H}, {UNIT_WEIGHT}, Optional_MASTER_CASE_L, Optional_MASTER_CASE_W, Optional_MASTER_CASE_H, Optional_MASTER_CASE_WEIGHT, Optional_CATAGORY, Optional_SUBCATAGORY, Optional_Columns1, Optional_Columns2, Optional_Columns3, Optional_Columns4, Optional_Columns5, Optional_Columns6})),
#"RenameColumns" = Table.RenameColumns(#"SelectColumns", {{SKU_Column, "SKU"}, {SKU_Description, "SKU Description"}, {UNITS_PER_CASE, "UNITS/CASE"}, {UNIT_L, "UNIT L"}, {UNIT_W, "UNIT W"}, {UNIT_H, "UNIT H"}, {UNIT_WEIGHT, "UNIT WEIGHT"}, {if MASTER_CASE_L then {MissingField.Ignore} else {MASTER_CASE_L,"MASTER CASE L"}}})
in #"RenameColumns",


//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,
SKU_Description as Column_Type,
UNITS_PER_CASE as Column_Type,
UNIT_L as Column_Type,
UNIT_W as Column_Type,
UNIT_H as Column_Type,
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 CATAGORY as Column_Type,
optional SUBCATAGORY 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)
as table,

//cast original function to be of new custom function type
ImportAndRenameV2 = Value.ReplaceType(
ImportAndRename,
MyFunctionType)

in 
ImportAndRenameV2

Edit: 
Current columns provided are different with every input file as they come from different sources. An example of the column structure of an input file is this:

If I populate the function with the below input, the output I'm currently getting is below (some of the column names below were not lined up to fit in the screenshot above):

The first few columns shown here have been successfully renamed, but the latter 3 in the above screenshot still have their original names. To allow the function to run, I had to remove the following from the #"RenameColumns" portion of the code.

{if MASTER_CASE_L then {MissingField.Ignore} else {MASTER_CASE_L,"MASTER CASE L"}

Thanks
 

[v-jingzhang]

Hi @RickBickens 

Can you please provide some dummy data or examples to show the column names before/after transformation? This could help us understand the expected result better. Thank you in advance. 

Best Regards,
Community Support Team _ Jing

[RickBickens]

Hi Community Support Team _ Jing,

I've editted my post to have some more supporting examples of the column transformation, let me know if that helps.

Thanks!

[v-jingzhang]

Hi @RickBickens 

I didn't use your function. I'm thinking if you can prepare a NameDict Table in advance to have all possible source column names (from source files) and the consistent names they should be renamed into, just like below. (Name, Dept and Group are consistent names and the latter ones are optional.)

Then you can create a custom function

(Input_Table as table) =>
let
    SourceNames = NameDict[Source],
    TargetNames = NameDict[Target],
    Source = Table.TransformColumnNames(Input_Table, each if List.Contains(SourceNames, _) then let _index = List.PositionOf(SourceNames, _) in TargetNames{_index} else _)
in
    Source

Invoke this function on any input table to rename their columns. You don't need to use parameters to enter column names. If a table has a column that is not present in the NameDict table, its name will not be changed. This means that you need to maintain the NameDict table before you rename columns of a table. 

I have attached the pbix. Hope it is helpful. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[RickBickens]

That is an option, though not ideal as it would mean generating that table each time (each source document is always different and can be significantly different) and then importing into power query. I feel like that may be the solution I need to use but I wanted to try again at explaining my error as I don't feel like I communicated it well the first time. 

My first iteration of the function is below:

let
//declare a function

ImportAndRename =
(SKU_Column as text, SKU_Description as text, UNITS_PER_CASE as text, UNIT_L as text, UNIT_W as text, UNIT_H as text, 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 CATAGORY as text, optional SUBCATAGORY 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) => let

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_CATAGORY = if CATAGORY = null then {} else {CATAGORY}, 
Optional_SUBCATAGORY = if SUBCATAGORY = null then {} else {SUBCATAGORY},
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_Descript = if Description = null then {} else {Description},

#"SelectColumns" = Table.SelectColumns(#"ITEM_INPUT (2)", List.Combine({{SKU_Column}, {SKU_Description}, {UNITS_PER_CASE}, {UNIT_L}, {UNIT_W}, {UNIT_H}, {UNIT_WEIGHT}, Optional_MASTER_CASE_L, Optional_MASTER_CASE_W, Optional_MASTER_CASE_H, Optional_MASTER_CASE_WEIGHT, Optional_CATAGORY, Optional_SUBCATAGORY, Optional_Columns1, Optional_Columns2, Optional_Columns3, Optional_Columns4, Optional_Columns5, Optional_Columns6})),
#"RenameColumns" = Table.RenameColumns(#"SelectColumns", {{SKU_Column, "SKU"}, {SKU_Description, "SKU Description"}, {UNITS_PER_CASE, "UNITS/CASE"}, {UNIT_L, "UNIT L"}, {UNIT_W, "UNIT W"}, {UNIT_H, "UNIT H"}, {UNIT_WEIGHT, "UNIT WEIGHT"}, {MASTER_CASE_L,"MASTER CASE L"},{MASTER_CASE_W,"MASTER CASE W"}, {MASTER_CASE_H,"MASTER CASE H"},{MASTER_CASE_WEIGHT,"MASTER CASE WEIGHT"},{CATAGORY,"CATAGORY"},{SUBCATAGORY,"SUBCATAGORY"}})

in #"RenameColumns",


//declare Column_Type list
Column_Type = type text
meta
[Documentation.Description = "Please select the SKU column",
Documentation.AllowedValues = Table.ColumnNames(#"ITEM_INPUT (2)")],

//declare custom function type using custom number types
MyFunctionType = type function(
SKU_Column as Column_Type,
SKU_Description as Column_Type,
UNITS_PER_CASE as Column_Type,
UNIT_L as Column_Type,
UNIT_W as Column_Type,
UNIT_H as Column_Type,
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 CATAGORY as Column_Type,
optional SUBCATAGORY 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)
as table,

//cast original function to be of new custom function type
ImportAndRenameV2 = Value.ReplaceType(
ImportAndRename,
MyFunctionType)

in 
ImportAndRenameV2

When I initially ran that with some of the optional inputs populated but not all of them I would get this error. 

Inputs used:

So my intention was to add conditional inclusion of the optional columns in #"RenameColumns" if they have values. I'm not sure how to do that without including if statements for every different iteration of the 6 optional columns being populated. Is it possible to have conditional inclusion like that?

If it is possible would I need to also conditionally include those optional columns in #"SelectColumns"?

[RickBickens · Accepted solution]

I was able to get it to work as I was hoping with the following function code.

let
//declare a function

ImportAndRename =
(SKU_Column as text, SKU_Description as text, UNITS_PER_CASE as text, UNIT_L as text, UNIT_W as text, UNIT_H as text, 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) => let

Optional_MASTER_CASE_L = if MASTER_CASE_L = null then {} else {MASTER_CASE_L},
Column_Name_MCL = if MASTER_CASE_L = null then MissingField.Ignore else MASTER_CASE_L,
Optional_MASTER_CASE_W = if MASTER_CASE_W = null then {} else {MASTER_CASE_W}, 
Column_Name_MCW = if MASTER_CASE_W = null then MissingField.Ignore else MASTER_CASE_W,
Optional_MASTER_CASE_H = if MASTER_CASE_H = null then {} else {MASTER_CASE_H},
Column_Name_MCH = if MASTER_CASE_H = null then MissingField.Ignore else MASTER_CASE_H,
Optional_MASTER_CASE_WEIGHT = if MASTER_CASE_WEIGHT = null then {} else {MASTER_CASE_WEIGHT},
Column_Name_MCWEIGHT = if MASTER_CASE_WEIGHT = null then MissingField.Ignore else MASTER_CASE_WEIGHT,
Optional_CATEGORY = if CATEGORY = null then {} else {CATEGORY}, 
Column_Name_CATEGORY = if CATEGORY = null then MissingField.Ignore else CATEGORY,
Optional_SUBCATEGORY = if SUBCATEGORY = null then {} else {SUBCATEGORY},
Column_Name_SUBCATEGORY = if SUBCATEGORY = null then MissingField.Ignore 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_Descript = if Description = null then {} else {Description},

#"SelectColumns" = Table.SelectColumns(#"ITEM_INPUT (3)", List.Combine({{SKU_Column}, {SKU_Description}, {UNITS_PER_CASE}, {UNIT_L}, {UNIT_W}, {UNIT_H}, {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})),
#"RenameColumns" = Table.RenameColumns(#"SelectColumns", {{SKU_Column, "SKU"}, {SKU_Description, "SKU Description"}, {UNITS_PER_CASE, "UNITS/CASE"}, {UNIT_L, "UNIT L"}, {UNIT_W, "UNIT W"}, {UNIT_H, "UNIT H"}, {UNIT_WEIGHT, "UNIT WEIGHT"}}),
#"RenameMCL" = if MASTER_CASE_L = null then #"RenameColumns" else Table.RenameColumns(#"RenameColumns",{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 (3)")],

//declare custom function type using custom number types
MyFunctionType = type function(
SKU_Column as Column_Type,
SKU_Description as Column_Type,
UNITS_PER_CASE as Column_Type,
UNIT_L as Column_Type,
UNIT_W as Column_Type,
UNIT_H as Column_Type,
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)
as table,

//cast original function to be of new custom function type
ImportAndRenameV2 = Value.ReplaceType(
ImportAndRename,
MyFunctionType)

in 
ImportAndRenameV2

[v-jingzhang]

Glad you have solved it by yourself! I will need some time to digest the function and update my knowledge. Thank you for your sharing! 

Best Regards,
Community Support Team _ Jing