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