Table.Profile List.Sum returns null Values

[hayden]

I have a Power Query that calculates the statistics including a sum of all the values using the Table.Profile

= Table.Profile(#"Removed Columns", { {"Sum", each Type.Is (_,type number),List.Sum}})

But the results show a value of null for several values that shouldn't be null, but should be positive values.

The values being passed are all numbers (for example the Den Dem Drought Stage column which only has 0 or 1 as a value).

Here are the results of the Table.Profile command:

Any ideas why I get null as a returned value, rather than a sum?

 

[JEC]

how about

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSUTJUitWJVkpKhDNB4kZAnoFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t]),
    cTypes = Table.TransformColumnTypes(Source,{{"b", Int64.Type}}),
    profiles = Table.Profile(cTypes),
    result = Table.AddColumn(profiles, "Sum", each List.Sum(Table.Column(Table.SelectRows(cTypes, (r) => Record.Field(r,_[Column]) = 1 ),_[Column])))
in
    result

 

[hayden]

Thanks JEC, Here is the full query I have, and I'm struggling to figure out how to mesh your code into it. Any guidance you have on fitting that into the query?

let
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(Filepath & "MONOUTCOL.CSV"),[Delimiter=",", Columns=46, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"     |||            |Year/Mo.    ", Int64.Type}, {"Node |Type|Option|Res_Account |Priority    ", Int64.Type}, {"50150|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"50300|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"50450|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {" 4250|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"57140|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"56100|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"53500|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51220|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51230|Res. Account|Storage contents|   10|-1.00          ", type number}, {" 3950|Res. Account|Storage contents|    1|-1.00          ", type number}, {" 3950|Res. Account|Storage contents|    3|-1.00          ", type number}, {" 1600|Res. Account|Storage contents|    1|-1.00          ", type number}, {"51750|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51670|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"58557|Res. Node Balance|Storage contents|   -1|-1.00          ", Int64.Type}, {"51020|Res. Node Balance|River outflow|   -1|-1.00          ", type number}, {"51020|Res. Account|Total storage release|   11|-1.00          ", type number}, {"51900|Water Right|Rule Yield|   57|1000.00        ", Int64.Type}, {"51900|Diversion Node Balance|Demand|   -1|-1.00          ", type number}, {"51900|Diversion Node Balance|Shortage|   -1|-1.00          ", Int64.Type}, {"51900|Water Right|Rule Yield|   76|9900000025.00  ", type number}, {"51900|Water Right|Rule-specific param 1|   76|9900000025.00  ", Int64.Type}, {"56500|Diversion Node Balance|Shortage|   -1|-1.00          ", Int64.Type}, {"56600|Diversion Node Balance|Shortage|   -1|-1.00          ", Int64.Type}, {"56650|Diversion Node Balance|Shortage|   -1|-1.00          ", Int64.Type}, {"56700|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56750|Diversion Node Balance|Shortage|   -1|-1.00          ", Int64.Type}, {"58420|Diversion User Balance|Shortage|    2|-1.00          ", Int64.Type}, {"51590|Diversion User Balance|Shortage|    1|-1.00          ", Int64.Type}, {"58075|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"58085|Diversion Node Balance|Shortage|   -1|-1.00          ", Int64.Type}, {"51250|Diversion User Balance|Shortage|    1|-1.00          ", Int64.Type}, {" 4240|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"   32|Pipe Data|Flow|   32|-1.00          ", type number}, {"51560|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"51650|Diversion User Balance|Total supply|    6|-1.00          ", type number}, {"58430|Water Right|Rule Yield|   11|900001.10      ", Int64.Type}, {"51750|Res. Account|River release - other|    1|-1.00          ", type number}, {"51670|Res. Account|River release - other|    1|-1.00          ", type number}, {"58557|Res. Account|River release - other|    1|-1.00          ", Int64.Type}, {"51241|Res. Account|Total supply|    1|-1.00          ", Int64.Type}, {"  145|Pipe Data|Flow|  145|-1.00          ", type number}, {"56400|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"||||", type any}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"     |||            |Year/Mo.    ", "Year"}, {"Node |Type|Option|Res_Account |Priority    ", "Month"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each #date([Year],[Month],1)),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Date", "Year", "Month", "50150|Res. Node Balance|Storage contents|   -1|-1.00          ", "50300|Res. Node Balance|Storage contents|   -1|-1.00          ", "50450|Res. Node Balance|Storage contents|   -1|-1.00          ", " 4250|Res. Node Balance|Storage contents|   -1|-1.00          ", "57140|Res. Node Balance|Storage contents|   -1|-1.00          ", "56100|Res. Node Balance|Storage contents|   -1|-1.00          ", "53500|Res. Node Balance|Storage contents|   -1|-1.00          ", "51220|Res. Node Balance|Storage contents|   -1|-1.00          ", "51230|Res. Account|Storage contents|   10|-1.00          ", " 3950|Res. Account|Storage contents|    1|-1.00          ", " 3950|Res. Account|Storage contents|    3|-1.00          ", " 1600|Res. Account|Storage contents|    1|-1.00          ", "51750|Res. Node Balance|Storage contents|   -1|-1.00          ", "51670|Res. Node Balance|Storage contents|   -1|-1.00          ", "58557|Res. Node Balance|Storage contents|   -1|-1.00          ", "51020|Res. Node Balance|River outflow|   -1|-1.00          ", "51020|Res. Account|Total storage release|   11|-1.00          ", "51900|Water Right|Rule Yield|   57|1000.00        ", "51900|Diversion Node Balance|Demand|   -1|-1.00          ", "51900|Diversion Node Balance|Shortage|   -1|-1.00          ", "51900|Water Right|Rule Yield|   76|9900000025.00  ", "51900|Water Right|Rule-specific param 1|   76|9900000025.00  ", "56500|Diversion Node Balance|Shortage|   -1|-1.00          ", "56600|Diversion Node Balance|Shortage|   -1|-1.00          ", "56650|Diversion Node Balance|Shortage|   -1|-1.00          ", "56700|Diversion Node Balance|Shortage|   -1|-1.00          ", "56750|Diversion Node Balance|Shortage|   -1|-1.00          ", "58420|Diversion User Balance|Shortage|    2|-1.00          ", "51590|Diversion User Balance|Shortage|    1|-1.00          ", "58075|Diversion Node Balance|Shortage|   -1|-1.00          ", "58085|Diversion Node Balance|Shortage|   -1|-1.00          ", "51250|Diversion User Balance|Shortage|    1|-1.00          ", " 4240|Diversion Node Balance|Total supply|   -1|-1.00          ", "   32|Pipe Data|Flow|   32|-1.00          ", "51560|Diversion Node Balance|Total supply|   -1|-1.00          ", "51650|Diversion User Balance|Total supply|    6|-1.00          ", "58430|Water Right|Rule Yield|   11|900001.10      ", "51750|Res. Account|River release - other|    1|-1.00          ", "51670|Res. Account|River release - other|    1|-1.00          ", "58557|Res. Account|River release - other|    1|-1.00          ", "51241|Res. Account|Total supply|    1|-1.00          ", "  145|Pipe Data|Flow|  145|-1.00          ", "56400|Diversion Node Balance|Total supply|   -1|-1.00          ", "||||"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns",{{"50150|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"50300|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"50450|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {" 4250|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"57140|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"56100|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"53500|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51220|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51230|Res. Account|Storage contents|   10|-1.00          ", type number}, {" 3950|Res. Account|Storage contents|    1|-1.00          ", type number}, {" 3950|Res. Account|Storage contents|    3|-1.00          ", type number}, {" 1600|Res. Account|Storage contents|    1|-1.00          ", type number}, {"51750|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51670|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"58557|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51020|Res. Node Balance|River outflow|   -1|-1.00          ", type number}, {"51020|Res. Account|Total storage release|   11|-1.00          ", type number}, {"51900|Water Right|Rule Yield|   57|1000.00        ", type number}, {"51900|Diversion Node Balance|Demand|   -1|-1.00          ", type number}, {"51900|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"51900|Water Right|Rule Yield|   76|9900000025.00  ", type number}, {"51900|Water Right|Rule-specific param 1|   76|9900000025.00  ", type number}, {"56500|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56600|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56650|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56700|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56750|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"58420|Diversion User Balance|Shortage|    2|-1.00          ", type number}, {"51590|Diversion User Balance|Shortage|    1|-1.00          ", type number}, {"58075|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"58085|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"51250|Diversion User Balance|Shortage|    1|-1.00          ", type number}, {" 4240|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"   32|Pipe Data|Flow|   32|-1.00          ", type number}, {"51560|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"51650|Diversion User Balance|Total supply|    6|-1.00          ", type number}, {"58430|Water Right|Rule Yield|   11|900001.10      ", type number}, {"51750|Res. Account|River release - other|    1|-1.00          ", type number}, {"51670|Res. Account|River release - other|    1|-1.00          ", type number}, {"58557|Res. Account|River release - other|    1|-1.00          ", type number}, {"51241|Res. Account|Total supply|    1|-1.00          ", type number}, {"  145|Pipe Data|Flow|  145|-1.00          ", type number}, {"56400|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"||||", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type3",{{"Date", Order.Ascending}}),
    #"Calc NEndRawShortage" = Table.AddColumn(#"Sorted Rows", "NEndRawShort", each List.Sum({[#"56500|Diversion Node Balance|Shortage|   -1|-1.00          "], [#"56600|Diversion Node Balance|Shortage|   -1|-1.00          "], [#"56650|Diversion Node Balance|Shortage|   -1|-1.00          "], [#"56700|Diversion Node Balance|Shortage|   -1|-1.00          "], [#"56750|Diversion Node Balance|Shortage|   -1|-1.00          "]}), type number),
    CalcNonPotableReuseShortage = Table.AddColumn(#"Calc NEndRawShortage", "PotableShort", each List.Sum({[#"51590|Diversion User Balance|Shortage|    1|-1.00          "], [#"58075|Diversion Node Balance|Shortage|   -1|-1.00          "], [#"58085|Diversion Node Balance|Shortage|   -1|-1.00          "]}), type number),
    CalUnusedReusable = Table.AddColumn(CalcNonPotableReuseShortage, "CalcUnusedReusable", each List.Sum({[#"51650|Diversion User Balance|Total supply|    6|-1.00          "], [#"58430|Water Right|Rule Yield|   11|900001.10      "], [#"51750|Res. Account|River release - other|    1|-1.00          "], [#"51670|Res. Account|River release - other|    1|-1.00          "], [#"58557|Res. Account|River release - other|    1|-1.00          "]}), type number),
    #"Select Columns to Keep" = Table.SelectColumns(CalUnusedReusable,{"51900|Water Right|Rule Yield|   57|1000.00        ", "51900|Diversion Node Balance|Demand|   -1|-1.00          ", "51900|Diversion Node Balance|Shortage|   -1|-1.00          ", "58420|Diversion User Balance|Shortage|    2|-1.00          ", "51590|Diversion User Balance|Shortage|    1|-1.00          ", "51250|Diversion User Balance|Shortage|    1|-1.00          ", " 4240|Diversion Node Balance|Total supply|   -1|-1.00          ", "   32|Pipe Data|Flow|   32|-1.00          ", "51560|Diversion Node Balance|Total supply|   -1|-1.00          ", "51241|Res. Account|Total supply|    1|-1.00          ", "NEndRawShort", "PotableShort", "CalcUnusedReusable"}),
    #"Rename Nodes" = Table.RenameColumns(#"Select Columns to Keep",{{"58420|Diversion User Balance|Shortage|    2|-1.00          ", "SACWSD"}, {"51590|Diversion User Balance|Shortage|    1|-1.00          ", "Cherokee"}, {" 4240|Diversion Node Balance|Total supply|   -1|-1.00          ", "Roberts Tunnel"}, {"   32|Pipe Data|Flow|   32|-1.00          ", "Moffat Tunnel"}, {"51560|Diversion Node Balance|Total supply|   -1|-1.00          ", "Burlington"}, {"51900|Diversion Node Balance|Shortage|   -1|-1.00          ", "Den Demand Shortage"}, {"51250|Diversion User Balance|Shortage|    1|-1.00          ", "S. Metro Wise Shortage"}, {"51241|Res. Account|Total supply|    1|-1.00          ", "WISE Trigger Vol"}, {"51900|Diversion Node Balance|Demand|   -1|-1.00          ", "Total Node Demand"}}),
    #"Remove -1 Drought Stage" = Table.AddColumn(#"Rename Nodes", "Den Dem Drought Stage", each if [#"51900|Water Right|Rule Yield|   57|1000.00        "] > -1 then 1 else 0),
    #"Changed Type4" = Table.TransformColumnTypes(#"Remove -1 Drought Stage",{{"Den Dem Drought Stage", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"51900|Water Right|Rule Yield|   57|1000.00        "}),
    #"Calculate Stats" = Table.Profile(#"Removed Columns", { {"Sum", each Type.Is (_,type number),List.Sum}}),
    #"Select Stats to Keep" = Table.SelectColumns(#"Calculate Stats",{"Column", "Max", "Average", "Count", "Sum"}),
    #"Replace Null Values" = Table.ReplaceValue(#"Select Stats to Keep",null,0,Replacer.ReplaceValue,{"Sum"}),
    #"Convert to Decimal Numbers" = Table.TransformColumnTypes(#"Replace Null Values",{{"Average", type number}, {"Sum", type number}, {"Max", type number}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Convert to Decimal Numbers",{{"Column", "Name"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"Name", "Average", "Max", "Sum"})
in
    #"Reordered Columns1"

 

[hayden]

@JEC Thanks for the reply. I think I have that poorly integrated into all my code, but I still don't get values for Sum.

let
    Source = Csv.Document(File.Contents(Path & "\DAYOUTCOL.CSV"),[Delimiter=",", Columns=46, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type",1000),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Set Datatypes" = Table.TransformColumnTypes(#"Promoted Headers",{{"50150|Res. Node Balance|Storage contents|   -1       |-1.00       ", type number}, {"50300|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"50450|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {" 4250|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"57140|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"56100|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"53500|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51220|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51230|Res. Account|Storage contents|   10|-1.00          ", type number}, {" 3950|Res. Account|Storage contents|    1|-1.00          ", type number}, {" 3950|Res. Account|Storage contents|    3|-1.00          ", type number}, {" 1600|Res. Account|Storage contents|    1|-1.00          ", type number}, {"51750|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51670|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"58557|Res. Node Balance|Storage contents|   -1|-1.00          ", type number}, {"51020|Res. Node Balance|River outflow|   -1|-1.00          ", type number}, {"51020|Res. Account|Total storage release|   11|-1.00          ", type number}, {"51900|Water Right|Rule Yield|   57|1000.00        ", type number}, {"51900|Diversion Node Balance|Demand|   -1|-1.00          ", type number}, {"51900|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"51900|Water Right|Rule Yield|   76|9900000025.00  ", type number}, {"51900|Water Right|Rule-specific param 1|   76|9900000025.00  ", type number}, {"56500|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56600|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56650|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56700|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"56750|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"58420|Diversion User Balance|Shortage|    2|-1.00          ", type number}, {"51590|Diversion User Balance|Shortage|    1|-1.00          ", type number}, {"58075|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"58085|Diversion Node Balance|Shortage|   -1|-1.00          ", type number}, {"51250|Diversion User Balance|Shortage|    1|-1.00          ", type number}, {" 4240|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"   32|Pipe Data|Flow|   32|-1.00          ", type number}, {"51560|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"51650|Diversion User Balance|Total supply|    6|-1.00          ", type number}, {"58430|Water Right|Rule Yield|   11|900001.10      ", type number}, {"51750|Res. Account|River release - other|    1|-1.00          ", type number}, {"51670|Res. Account|River release - other|    1|-1.00          ", type number}, {"58557|Res. Account|River release - other|    1|-1.00          ", type number}, {"51241|Res. Account|Total supply|    1|-1.00          ", type number}, {"  145|Pipe Data|Flow|  145|-1.00          ", type number}, {"56400|Diversion Node Balance|Total supply|   -1|-1.00          ", type number}, {"Node |Type|Option|Res_Account |Priority    ", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Set Datatypes",{"||||", "||||_1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Node |Type|Option|Res_Account |Priority    ", "Date"}}),
    #"Create SSFishLookup" = Table.AddColumn(#"Renamed Columns", "SSFishLookup", each #date(2000,Date.Month([Date]),Date.Day([Date])), type date),
    #"Calc SSFishRate" = Table.AddColumn(#"Create SSFishLookup", "SSFishRate", each if [SSFishLookup]<FFDate2 then FFRate1 else (if [SSFishLookup]<FFDate3 then FFRate2 else FFRate3), type number),
    #"Added Custom" = Table.AddColumn(#"Calc SSFishRate", "SSFishShortage", each if [SSFishRate]-[#"51020|Res. Node Balance|River outflow|   -1|-1.00          "] >0 then [SSFishRate]-[#"51020|Res. Node Balance|River outflow|   -1|-1.00          "] else 0, type number),
    #"Select Col to Keep" = Table.SelectColumns(#"Added Custom",{"SSFishShortage", "51900|Diversion Node Balance|Shortage|   -1|-1.00          ", "51900|Diversion Node Balance|Demand|   -1|-1.00          "}),
    #"Renamed Columns2" = Table.RenameColumns(#"Select Col to Keep",{{"51900|Diversion Node Balance|Demand|   -1|-1.00          ", "Total Node Demand"}, {"51900|Diversion Node Balance|Shortage|   -1|-1.00          ", "Total Node Shortage"}}),
    Source2 = #"Renamed Columns2",
    cTypes= Table.TransformColumnTypes(Source2,{{"SSFishShortage", Int64.Type}, {"Total Node Shortage", Int64.Type}, {"Total Node Demand", Int64.Type}}),
    profiles = Table.Profile(cTypes),
    result = Table.AddColumn(profiles, "Sum", each List.Sum(Table.Column(Table.SelectRows(cTypes, (r) => Record.Field(r,_[Column]) = 1 ),_[Column]))),
    #"Removed Other Columns" = Table.SelectColumns(result,{"Column", "Max", "Average", "Sum"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Column", "Max", "Sum"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Column", "Name"}})
in
    #"Renamed Columns1"

Here is my table after the transform column types:

And here is the resulting table.profile statistics.

The sum is still null...

I think I may get away from the Table.profile and instead load all of the data into the data model and then use powerpivot to do these statistics... Not sure if that will ultimately be faster but at least I think I can make that work,

Any feedback you may have would be welcome. Thanks!