I am trying to add a column if one does not already exist.

[gheyman]

I thought that the below was the correct formula but I am getting an error. I thought this checks and if there wasnt already a column named "F. > 1 Year" that it would add it and if there was one it would just go to the next step

= try Table.AddColumn(#"Pivoted Column", "F. > 1 Year", each null, Int64.Type) otherwise #"Pivoted Column"

let
Source = Table.NestedJoin(PBoM_UTable, {"Part Number"}, #"ModelPropricer vdataNISMaterialCostSource", {"PartNumber"}, "ModelPropricer vdataNISMaterialCostSource", JoinKind.LeftOuter),
#"Filtered Rows" = Table.SelectRows(Source, each ([#"Make/Buy"] = "Buy")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Level", "Type", "Assembly", "Make/Buy", "Qty Per", "Resource", "Curve"}),
#"Expanded ModelPropricer vdataNISMaterialCostSource" = Table.ExpandTableColumn(#"Removed Columns", "ModelPropricer vdataNISMaterialCostSource", {"Category", "Subcategory", "FromQty", "ToQty", "MinBuyQty", "UnitCost", "EXCESS", "NRE", "TARIFF", "StartDate", "EndDate", "Aged", "VendorName", "LeadTime", "MaterialCostSourceComments", "VendorQuoteNumber", "Created by", "Source Id", "Revision Id", "HyperLink", "Commodity", "EscalationID"}, {"Category", "Subcategory", "FromQty", "ToQty", "MinBuyQty", "UnitCost", "EXCESS", "NRE", "TARIFF", "StartDate", "EndDate", "Aged", "VendorName", "LeadTime", "MaterialCostSourceComments", "VendorQuoteNumber", "Created by", "Source Id", "Revision Id", "HyperLink", "Commodity", "EscalationID"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded ModelPropricer vdataNISMaterialCostSource", {"Part Number"}, #"RFQ Tracker Count", {"Part Number"}, "RFQ Tracker Count", JoinKind.LeftOuter),
#"Expanded RFQ Tracker Count" = Table.ExpandTableColumn(#"Merged Queries", "RFQ Tracker Count", {"RFQ Count", "RFQs Rcvd", "NoBidCount", "RFQs SentOut"}, {"RFQ Count", "RFQs Rcvd", "NoBidCount", "RFQs SentOut"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded RFQ Tracker Count", {"Part Number", "EndItem"}, BEQ_Qtys, {"Part Number", "EndItem"}, "BEQ_Qtys", JoinKind.LeftOuter),
#"Expanded BEQ_Qtys" = Table.ExpandTableColumn(#"Merged Queries2", "BEQ_Qtys", {"Ext BEQ Qty"}, {"Ext BEQ Qty"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded BEQ_Qtys", {"EndItem", "Part Number", "Source Id", "Revision Id", "FromQty", "ToQty", "MinBuyQty", "UnitCost", "EXCESS", "NRE", "TARIFF"}, Closest2BEQ, {"EndItem", "Part Number", "Source Id", "Revision Id", "FromQty", "ToQty", "MinBuyQty", "UnitCost", "EXCESS", "NRE", "TARIFF"}, "Closest2BEQ", JoinKind.LeftOuter),
#"Expanded Closest2BEQ" = Table.ExpandTableColumn(#"Merged Queries3", "Closest2BEQ", {"Closest BEQ Qty"}, {"Closest BEQ Qty"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Closest2BEQ", {"EndItem", "Part Number", "Category", "Subcategory", "Source Id"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"EndItem", "Part Number", "Category"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"ALL", each _, type table [EndItem=text, Part Number=text, Part Description=any, Using SimilarTo 4 Part Number=any, Category=nullable text, Subcategory=nullable text, FromQty=nullable number, ToQty=nullable number, MinBuyQty=nullable number, UnitCost=nullable number, EXCESS=nullable number, NRE=nullable number, TARIFF=nullable number, StartDate=nullable date, EndDate=nullable date, Aged=nullable text, VendorName=nullable text, LeadTime=nullable number, MaterialCostSourceComments=nullable text, VendorQuoteNumber=nullable text, Created by=nullable text, Source Id=nullable text, Revision Id=nullable text, HyperLink=nullable text, Commodity=nullable text, EscalationID=nullable text, RFQ Count=nullable number, RFQs Rcvd=nullable number, NoBidCount=nullable number, RFQs SentOut=nullable number, Ext BEQ Qty=nullable number, Closest BEQ Qty=nullable text]}}),
#"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Using SimilarTo 4 Part Number", "Aged", "RFQ Count", "RFQs Rcvd", "NoBidCount", "RFQs SentOut"}, {"Using SimilarTo 4 Part Number", "Aged", "RFQ Count", "RFQs Rcvd", "NoBidCount", "RFQs SentOut"}),
#"Added Custom" = Table.AddColumn(#"Expanded ALL", "Value", each 1),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Category] = "Quote")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Aged]), "Aged", "Value", List.Sum),
#"Added Custom1" = try Table.AddColumn(#"Pivoted Column", "F. > 1 Year", each null, Int64.Type) otherwise #"Pivoted Column"
in
#"Added Custom1"

 

[gheyman]

Note: The Pivot is where the "F. > 1 Year" is originally created. I have 6 categories (A-F) that may get added using the Pivot. If they dont I need to add the ones that did not have data and therefore were not created by the Pivot. Hope this all makes sense.

 

[GraH]

I would not try to add it, but check if it exists. Something like List.Contains(Table.ColumnNames(#"Pivoted Column"), "F.> Year"). wrapped in an if then else statement.

Seems that a lot of steps can be optimized in your query. A few things I notice:
- Why filter after a merge step? It's often best to reduce the number of lines before any merge.
- Also in this step #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"EndItem", "Part Number", "Category"}, you perform an operation on to many lines, because you have #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Category] = "Quote")),. Why not apply that filter from the start, before grouping at least.
- Same goes for removing columns. At each step these values get stored in memory.
- Some mergers seem to create multiplied records, so you remove duplicates. Maybe you can do that through an intermediate query where already 2 tables get linked. It might not be possible in the given use case, but something to consider.
- Buffering the tables before merging often speeds up the thing as well.

 

[gheyman]

Thanks. I used:

= if List.Count(List.FindText(Table.ColumnNames(#"Added F"), "D. 90-180 Days" ))= 1 then #"Added F" else Table.AddColumn(#"Added F", "D. 90-180 Days", each "", type text)

And thanks for the tips and enhancing the efficiency - very helpful and appreciated.

 

[GraH]

Feedback is appreciated too.
Glad you found the tips useful.