1

I am having troubles adding a column that takes the difference between two columns, because the one column doesn't exist.

Below is the formula I am using

= Table.AddColumn(#"Pivoted Column", "InventoryAvailabletoShip", each [On Hand Inventory] - [Third Party Storage])

Is there a way if the column doesn't exist then an empty column gets created so that the difference formula works to create the new column?

Olly
  • 7,749
  • 1
  • 19
  • 38
MLS
  • 108
  • 14
  • Why don't you just have that column in there from the beginning? Conditionally creating a column that must be empty by design seems really odd. – Ryan B. Mar 26 '19 at 15:48
  • The table is created from a SQL query and if I change the date range the value may or may not exist in the table – MLS Mar 26 '19 at 16:08

2 Answers2

3

You can use try .. otherwise to handle errors. Maybe something like:

= Table.AddColumn(#"Pivoted Column", "InventoryAvailabletoShip", each (try [On Hand Inventory] otherwise 0) - (try [Third Party Storage] otherwise 0))
Olly
  • 7,749
  • 1
  • 19
  • 38
2

If you are comfortable working in the advanced editor -- this is the best way to add the column you need (here I am assuming that 'Third Party Storage' is the column that may or may not exist):

AddedCustom = if Table.HasColumns(PriorStepName, "Third Party Storage") then PriorStepName else Table.AddColumn(PriorStepName, "Third Party Storage", each 0)

This will go in just before your calculated column is added. If "Third Party Storage" exist, it effectively does nothing. If the column does not exist, it adds it and populates every row with Zero.

Ryan B.
  • 3,575
  • 2
  • 20
  • 26