data type changes after custom column created, throws data conversion errors

amyfb

New member
Joined
Dec 18, 2020
Messages
24
Reaction score
0
Points
1
Location
Southeastern PA, USA
Excel Version(s)
O365
I have merged and appended a bunch of queries and ran into some data type issues i don't know how to resolve.
This is the first Query where the data type is set to decimal. It has calculated values needed by two other queries.
This query is merged with each of the other queries as a standalone step - because each needs different information from the first.
Those two merged queries are then appended into a final query.

Problem: below, in the Add Column (newLTFC, newT9, newThreshold) the resulting values are mixed type abc/123 and have to be changed to decimal.
when the merge takes place, this column reverts back to the abc/123 and the calculated columns contain errors due to inability to convert data to number.

Attempted solutions:
All null values from columns needed for calculations were converted to 0
All columns needed for calcs double checked for data type set to Decimal.

Is my issue coming from the merge/append sequence?
Is there another cause that anyone can guess about?

all ideas welcome,

I'm stumped.

thanks
Amy




Code:
let
    Source = Odbc.Query("dsn=PB_LIVE", "SELECT DISTINCT .........ORDER BY OV_INTERNAL_LOOKUP.ITEM_CODE ASC#(lf);"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ITEM_CODE", "NAME", "PRICE_LIST", "SOURCE", "DF", "FF", "T9", "PER_CASE", "TFC", "WEIGHT", "LTFC"}),
    #"add newFF value 5.04" = Table.AddColumn(#"Removed Other Columns", "newFF", each 5.04),
    #"change factors to type decimal" = Table.TransformColumnTypes(#"add newFF value 5.04",{{"newFF", type number}, {"DF", type number}, {"FF", type number}, {"T9", type number}}),
    #"replace null with 0 everywhere" = Table.ReplaceValue(#"change factors to type decimal",null,0,Replacer.ReplaceValue,{"DF", "FF", "T9", "PER_CASE", "TFC", "WEIGHT", "LTFC", "newFF"}),
    #"replace null with 0 in price list" = Table.ReplaceValue(#"replace null with 0 everywhere",null,"0",Replacer.ReplaceValue,{"PRICE_LIST"}),
  
  
    #"calc new LTFC" = Table.AddColumn(#"replace null with 0 in price list", "newLTFC", each if [DF]>0 and [TFC] > 0 and [SOURCE] <> null then 

if [SOURCE] = "UK" or [SOURCE] = "USA" then [LTFC] else 

if [SOURCE] = "Italy" then ( ([TFC] * [DF]) + ([WEIGHT] * [newFF])) else "check"

else "0"),
    #"Changed Type" = Table.TransformColumnTypes(#"calc new LTFC",{{"newLTFC", type number}}),
    #"calc new T9" = Table.AddColumn(#"Changed Type", "newT9", each [newLTFC] * [T9]),
    #"calc old threshold" = Table.AddColumn(#"calc new T9", "oldThreshold", each ( ([LTFC]*[T9]) - [LTFC]) / 2 + [LTFC]),
    #"calc new threshold" = Table.AddColumn(#"calc old threshold", "newThreshold",  each ( ([newT9] - [newLTFC]) / 2) + [newLTFC]),
    #"Changed Type1" = Table.TransformColumnTypes(#"calc new threshold",{{"newT9", type number}, {"oldThreshold", type number}, {"newThreshold", type number}})
in
    #"Changed Type1"
 
Just doing some general experimentation with AddColumn; there is another argument you can add which seems to work here, instead of:
Code:
Table.AddColumn(#"Changed Type", "newFF", each 5.04)
add the 4th:
Code:
Table.AddColumn(#"Changed Type", "newFF", each 5.04, type number)
and this seems to work too:
Code:
Table.AddColumn(#"Changed Type", "newFF", each 5.04, Number.Type)

edit: just realised this thread is 6 weeks old.
 
Back
Top