Table.AddColumn

[gheyman]

Im am pivoted a column and creating new columns from the values. In the column that is being Pivoted there may or may not be the values "Vendor NRE" and/or "Excess Material"

The Pivot works as expected.

What I am trying to do is create a "Vendor NRE" and/or an "Excess Material" column if the Pivot function did not result in creating these.

So I added the SQL code to add them - but its not working

In my test run - there is the value "Excess Material" in the Resource Type - so it gets created at that line of code

 #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[#"Resource Type"]), "Resource Type", "Amount", List.Sum),

But for some reason the next line removes the newly created column "Excess Material"

#"Added Custom" = try Table.AddColumn(#"Filtered Rows", "Vendor NRE", each null, Currency.Type) otherwise #"Filtered Rows",

This does create a Vendor NRE column and the line of code after that does create an Excess Material Code

But my problem is the Column that is created at the Pivot step goes away. I dont want it to. I only want to Add Columns if they dont previously exist.

Any help is appreciated

let
    // Reference Table from CMCS_BoM_MakeTable
    Source = CMCS_BoM_MakeTable,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "Assoc Cost")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"TASK ID", "Task Qty", "Column1", "Column2", "Column3", "WBS", "Type", "Assembly/Part", "Column4", "Assembly/Part Description", "Make/Buy", "UM", "Base Unit Cost", "Escalation Factor", "Quantity", "Extended Task Qty", "Delivery/Ship Qty", "Extended Qty", "Unit Cost", "Extended Cost", "Cost Type", "Cost Subcategory", "Cost Source", "Cost From Date", "Cost To Date", "Cost Esc. Base Date", "Cost Escalation ID", "Commodity", "Vendor", "Cost Lead Time", "Rule", "SimTo For Part", "Task Description", "PART NUMBER", "MTRL CONSOLIDATION", "OBS", "Material / Reference", "Resource", "CLIN", "SUMMARY", "Resource Type", "Amount"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Assembly/Part", "TASK ID", "Assembly/Part Description", "Material / Reference", "Resource", "Resource Type", "Amount"}),
    // Pivot is where a Vendor NRE and/or Excess Material column is created if "Vendor NRE" and/or "Excess Material" is listed in the Resource Type Column
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[#"Resource Type"]), "Resource Type", "Amount", List.Sum),
    // Add the Column Vendor NRE is it does not exist
    #"Added Custom" = try Table.AddColumn(#"Filtered Rows", "Vendor NRE", each null, Currency.Type) otherwise #"Filtered Rows",
    // Add the Column Excess Material is it does not exist
    #"Added Custom1" = try Table.AddColumn(#"Added Custom", "Excess Material", each null, Currency.Type) otherwise #"Added Custom",
    #"Replaced NRE null w0" = Table.ReplaceValue(#"Added Custom1",null,0,Replacer.ReplaceValue,{"Vendor NRE"}),
    #"Replaced Excess null w0" = Table.ReplaceValue(#"Replaced NRE null w0",null,0,Replacer.ReplaceValue,{"Excess Material"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Excess null w0",{{"Vendor NRE", Currency.Type}, {"Excess Material", Currency.Type}})
in
    #"Changed Type"

 

[RoryA]

You passed #"Filtered Rows" as the table, not #"Pivoted Column"

 

[gheyman]

Thanks, that got it to add Vendor NRE

But know I get

Expression.Error: The field 'Excess Material' already exists in the record.
Details:
Name=Excess Material
Value=

When its checking for the Excess. In this instance the Pivot already created a column called Excess Material.
I though this code was checking to see if it already exists (which it does) and only of it does not will it try to create a new column named Excess Material

Thank you for your time and help.

 

[gheyman]

I guess its the order of steps. I moved #"Replaced NRE....." above the Add Custom1 and that seems to have fixed it. To be honest, I dont understand why but it now works.

let
    // Reference Table from CMCS_BoM_MakeTable
    Source = CMCS_BoM_MakeTable,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "Assoc Cost")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"TASK ID", "Task Qty", "Column1", "Column2", "Column3", "WBS", "Type", "Assembly/Part", "Column4", "Assembly/Part Description", "Make/Buy", "UM", "Base Unit Cost", "Escalation Factor", "Quantity", "Extended Task Qty", "Delivery/Ship Qty", "Extended Qty", "Unit Cost", "Extended Cost", "Cost Type", "Cost Subcategory", "Cost Source", "Cost From Date", "Cost To Date", "Cost Esc. Base Date", "Cost Escalation ID", "Commodity", "Vendor", "Cost Lead Time", "Rule", "SimTo For Part", "Task Description", "PART NUMBER", "MTRL CONSOLIDATION", "OBS", "Material / Reference", "Resource", "CLIN", "SUMMARY", "Resource Type", "Amount"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Assembly/Part", "TASK ID", "Assembly/Part Description", "Material / Reference", "Resource", "Resource Type", "Amount"}),
    // Pivot is where a Vendor NRE and/or Excess Material column is created if "Vendor NRE" and/or "Excess Material" is listed in the Resource Type Column
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[#"Resource Type"]), "Resource Type", "Amount", List.Sum),
    // Add the Column Vendor NRE is it does not exist
    #"Added Custom" = try Table.AddColumn(#"Pivoted Column", "Vendor NRE", each null, Currency.Type) otherwise #"Pivoted Column",
    #"Replaced NRE null w0" = Table.ReplaceValue(#"Added Custom",null,0,Replacer.ReplaceValue,{"Vendor NRE"}),
    // Add the Column Excess Material is it does not exist
    #"Added Custom1" = try Table.AddColumn(#"Replaced NRE null w0", "Excess Material", each null, Currency.Type) otherwise #"Replaced NRE null w0",
    #"Replaced Excess null w0" = Table.ReplaceValue(#"Added Custom1",null,0,Replacer.ReplaceValue,{"Excess Material"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Excess null w0",{{"Vendor NRE", Currency.Type}, {"Excess Material", Currency.Type}})
in
    #"Changed Type"

 

[RoryA]

Instead of using the error handler, you could do something like:

if List.Contains(Table.ColumnNames(#"Pivoted Column"), "Vendor NRE") then #"Pivoted Column" else Table.AddColumn(#"Pivoted Column", "Vendor NRE", each null, Currency.Type),

 

[gheyman]

Thanks, I tried that but when it created the Vendor NRE column it created it as an Expandable column (had the double arrows to the right of the column name and the row values were all "Table".

Thank you Rory

 

[RoryA]

It works fine for me. Just adds a new blank currency column.