Issue Adding Column Only if it Doesn't Exists

Question

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?

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.
– Ryan B., Commented Mar 26, 2019 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
– MLS, Commented Mar 26, 2019 at 16:08

Olly · Accepted Answer · 2019-03-26 12:43:30Z

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))

answered Mar 26, 2019 at 12:43

Olly

7,9021 gold badge23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ryan B. · Accepted Answer · 2019-03-26 16:33:04Z

3

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.

answered Mar 26, 2019 at 16:33

Ryan B.

3,6852 gold badges22 silver badges28 bronze badges

Collectives™ on Stack Overflow

Issue Adding Column Only if it Doesn't Exists

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related