Need assistance with Power Query If Statement for custom columns

[vbaNumpty]

I have a column that has case quantities in them, due to the nature of the data entry sometimes this column has some text in it which messes up the conversions. I am trying to make an if statment that will see if the value contains text and transform it appropriately.

Quick example, the three columns that wold be in use (3rd row after the headers is where I need the if statement to work):

Cases	Pack Size	Pots
5	15	75
10	18	180
12 pots	Other	ERROR
10	18	180

What I would want the if statement to do is the following: If [pack size] = "Other" then Pots = [cases] but removing the 5 rightmost characters so that it would just be 12
Else
[cases]*[pack size]

Hoping someone could help me out in the proper syntax for achieving the above.

 

[GraH]

Try ... Otherwise, can work like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cases", type any}, {"Pack Size", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Pots", each try [Cases]*[Pack Size]
otherwise Number.FromText(Text.Select([Cases],{"0".."9"})))
in
    #"Added Custom"

 

[vbaNumpty]

Try ... Otherwise, can work like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cases", type any}, {"Pack Size", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Pots", each try [Cases]*[Pack Size]
otherwise Number.FromText(Text.Select([Cases],{"0".."9"})))
in
    #"Added Custom"

When I paste the code it tells me the column cases wasn't found because the original data source is a dynamic range and I clean it up to get the proper headers through transformation.

 

[GraH]

What are the column names in your Power Query?
The formula should work in an added custom column:

try [Cases]*[Pack Size]
otherwise Number.FromText(Text.Select([Cases],{"0".."9"}))

 

[vbaNumpty]

What are the column names in your Power Query?
The formula should work in an added custom column:

try [Cases]*[Pack Size]
otherwise Number.FromText(Text.Select([Cases],{"0".."9"}))

The column names in the Power Query are as shown in the table above, Cases, Pack Size, Etc.

 

[vbaNumpty]

What are the column names in your Power Query?
The formula should work in an added custom column:

try [Cases]*[Pack Size]
otherwise Number.FromText(Text.Select([Cases],{"0".."9"}))

I got this to partially work, it now puts the number for pots (12 in the example I had placed above) but it gives Error for everything else that is a straight multiplication. It says

Expression.Error: We cannot convert the value 2222 to type Text.
Details:
Value=2222
Type=[Type]

 

[GraH]

Hi,

Do you have a step with Change Type? Delete it, so we keep it a variant, or better any, type. Numbers are treated as numbers and text as text. Else you need to use

Number.FromText (Column)

for every single argument of the formula.