Search code examples
excelpowerbipowerquerypowerpivotm

Power Query Column Average Cannot Convert Null to Type List

I have a column (First Salary) that has either a number of a null value. I added another column (CSM_SALARY) to check if it is null, and if so, take the average of First Salary, if not, take the value in Fist Salary. However, when computing the average, it returns the following error:

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

I have tried replacing the null values with numbers but receive the exact same error with it saying the number cannot be converted.

Is there anyway to take the average of a column and ignore null values?

 #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns5",{{"FIRST_SALARY", Int64.Type}}),
 #"Added Custom2" = Table.AddColumn(#"Changed Type", "CSM_SALARY", each if [SFDC_ID] = "Internal" then 0 else if [FIRST_SALARY] = null then List.Average([FIRST_SALARY]) else [FIRST_SALARY]),

DATA BEFORE ERROR: DATA

DATA AFTER ERROR DATA

Solution

  • You're very close

    List.Average([FIRST_SALARY])

    Passes a single scalar value to List.Average. Instead it should be

    List.Average(#"Changed Type"[FIRST_SALARY])

    eg

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorVwSAMjQ2AgCKmAYQZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FIRST_SALARY = _t]),
    Data = Table.TransformColumnTypes(Source,{{"FIRST_SALARY", Int64.Type}}),
    AddCol = Table.AddColumn(Data, "CSM_SALARY", each if [FIRST_SALARY] = null then List.Average(Data[FIRST_SALARY]) else [FIRST_SALARY])
in
    AddCol