Declaring a data type power query

[randymone]

I am trying to create a FIFO cost of goods sold calculation from information i gleaned over the internet.    I am having an issue with the code.   If I define the "units" as a Int64.Type, it creates a whole number and the cost of goods sold is calculated, albeit the wrong amount due to the truncation of the amount of units.

If I define "units" as a decimal type, the cost of goods sold calculated is null.  Hoping someone can help me out.  Here is the code.

let
Source = TRANSACTIONS,
#"Renamed Columns" = Table.RenameColumns(Source,{{"aType", "Type"}, {"SECURITY", "Symbol"}, {"SHARES", "Units"}, {"PRICE", "Value per Unit"}, {"TOTAL VALUE", "Total Value"}}),
SortDateID = Table.Sort(#"Renamed Columns", {{"DATE", Order.Ascending}}),
RemoveErrors = Table.ReplaceErrorValues(SortDateID, {{"Units", 0}, {"Value per Unit", 0}}),
ReplaceNulls = Table.ReplaceValue(RemoveErrors, null, 0, Replacer.ReplaceValue, {"Units", "Value per Unit"}),
RemoveCol = Table.RemoveColumns(ReplaceNulls,{"Total Value", "Index"}),
// ReplaceNaN = Table.ReplaceValue(#"Removed Columns", #nan, 0, (value, old, new) => if Number.IsNaN(value) then new else value, {"Units", "Value per Unit"}),
ChColType = Table.TransformColumnTypes(RemoveCol, {{"Units", type number}, {"Value per Unit", type number}}),
AddTotalValue = Table.AddColumn(ChColType, "Total Value", each [Value per Unit] * [Units], type number),
GroupRows = Table.Group( AddTotalValue, {"Symbol"},
{
{"AllRows", each
let
t= Table.AddIndexColumn( Table.Sort( _,{{"Type", Order.Ascending}, {"DATE", Order.Ascending}}), "Index", 0, 1 ),

BuysOnly = Table.SelectRows( t, each ([Type] = "B")),
AddAbsUnits = Table.AddColumn( BuysOnly, "Abs Units", each Number.Abs([Units]), type number),
AddAbsValue = Table.AddColumn( AddAbsUnits, "Abs Total", each Number.Abs([Total Value]), type number),
GroupBuys = Table.Group( AddAbsValue, {"Symbol", "Type", "Abs Units", "Abs Total"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Sum Units", each List.Sum([Units]), type number},
{"AllRows2", each
let
buys = Table.Sort( _, {{"Units", Order.Ascending}, {"Index", Order.Ascending}}),
index2 = Table.AddIndexColumn( buys, "n", 1, 1, Int64.Type),
running = Table.AddColumn( index2, "RT", each List.Sum( List.FirstN( index2[Units], [n] )), type number),
result = Table.SelectRows( running, each [RT] >0,type number )[[Units] , [Value per Unit]]

in
result, type table [Units, Value per Unit]
}
}),
NoNulls = Table.SelectRows( GroupBuys, each ([Sum Units] <> 0), type number)[[AllRows2]],
ExpandResult = Table.ExpandTableColumn( NoNulls, "AllRows2", {"Units", "Value per Unit"}, {"Units", "Value per Unit"}),

lCost = List.Buffer( List.Combine( Table.AddColumn( ExpandResult, "Value", each List.Repeat( {[Value per Unit]}, [Units]))[Value])),
AddRecord = Table.AddColumn( t, "Values", each
[
i = [Index],
u = [Units],
CumPrevSell = List.Sum( Table.SelectRows( t[[Type], [Units], [Index]], each ([Type] = "S" and [Index] < i))[Units] ),
Sold Cost = let pSell = if CumPrevSell = null or CumPrevSell-1 < 0 then 0 else CumPrevSell in
try
if [Type] = "S" then List.Sum( List.FirstN( List.Skip( lCost, pSell ), u, type number )) else null
otherwise null
]
)
in
AddRecord, type table [DATE=nullable number, Symbol=nullable number, Units=nullable number, Value per Unit=nullable number, Type=nullable text, Total Value=nullable number, Index=nullable number]
}
} )[[AllRows]],
ExpandAllRows = Table.ExpandTableColumn(GroupRows, "AllRows", {"DATE", "Symbol", "Units", "Value per Unit", "Type", "Total Value", "Index", "Values"}, {"DATE", "Sybol", "Units", "Value per Unit", "Type", "Total Value", "Index", "Values"}),
GetCost = Table.ExpandRecordColumn(ExpandAllRows, "Values", {"Sold Cost"}, {"Sold Cost"})

in
GetCost

Thanks for any insight.

Randymone

[JoeBarry]

Hi @randymone 

Maybe the data coming from the source has a different locale that what your PBIX has? For example, decimal seperators from the source are . and your pbix is , this in my expierence causes issues

If this is the case, highlight all columns with a value in it and replace the . with , or change the locale in your PBIX

Hope fully this helps

Joe

If this post helps, then please Accept it as the solution

[randymone]

Thanks for the response.

This is a personal project and I am not using PowerBI, only Power Query.  The regional settings for the current workbook indicate the United States.  Transactions, the source of the data, is another Power Query which transforms the uploaded raw data.  The columns were renamed in #"Renamed Columns" to be consistent with the the code i found on the internet.

In debugging a bit further, it appears the issue is with the code:

    try
        if [Type] = "S" then List.Sum( List.FirstN( List.Skip(lCost, pSell), u)) else null
       otherwise null

which appears to create the null for the Cost Sold.     (I changed it to otherwise "xxx"  which was the result for a sale transaction.)

I am not sophisticated enough to parse the code to be able to see what happens in the individual steps (and see what the values are being used for lcost, psell, and U.) to troubleshoot the issue.   

Wondering whether the List.Skip function would ever provide a result if the data types being used are inconsistent.

Any insight would be appreciated.

Randymone