locked
Data type conversion causing errors in unrelated columns RRS feed

  • Question

  • I am creating a column "[DaysAvailable]" using the difference between two columns "[DateAvailable], [WONeededBy]" that are formatted as Date/Time.  I used Number.From as part of the column creation to convert the resulting data in the new [DaysAvailable] column into a number.  The problem is that when I preform this conversion it is causing errors in 4 unrelated column.  If I leave the resulting data unconverted everything is fine.  All 4 columns giving errors do use [WONeededBy] to calculate their values but [DaysAvailable] does not reference the 4 error columns in any way and vice-versa. 

    I tried using Table.TransformColumnTypes as an additional step to convert the data type of [DaysAvailable] to Int64 and got the same results.  

    I also tried leaving the new [DaysAvailable] column format alone and created an additional column using Number.From[DaysAvailable] and this only caused one unrelated column to give errors.

    Another function I tried was Duration.Days([WONeededBy] - [DateAvailable])).  This resulted in the same 4 columns with errors.  It seems that something about this new column being a number is causing these other columns to throw errors.

    If I view any of the errors from any of scenarios this is what it's giving me:

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

    None of the fields that are being referenced to produce the values in these error fields are null.  They all have valid values in them and produce the correct results until I change the data type of this totally unrelated [DaysAvailable] Column. Gah!  

    Here is the code line for the new column that creates the errors when preformed.

    = Table.AddColumn(#"Added CompleteOntimeQtr", "DaysAvailable", 
       each Number.From([WONeededBy] - [DateAvailable]))


    Here is the code line that creates the only column that gives an error when I preform the type conversion to a secondary new column.

    = Table.AddColumn(#"Added DueMo", "CompleteOntimeFY", 
         each if [FY] = fParameters("Parameters",4) 
         and [Completed] < [WONeededBy] then 1 else 0)


    Here's the code lines for the 3 other columns that give errors when I do the type conversion as part of the initial column creation.

    = Table.AddColumn(#"Added FY", "CompleteLateMo", 
       each if ([Completed] > [WONeededBy] 
       and Date.Month([WONeededBy]) = fParameters("Parameters",3)) then 1 else 0)
    
    = Table.AddColumn(#"Added CompleteLateMo", "CompleteOntime", 
       each if ([Completed] < [WONeededBy] 
       and Date.Month([WONeededBy]) = fParameters("Parameters",3)) then 1 else 0)
    
    = Table.AddColumn(#"Added DueQtr", "CompleteOntimeQtr", 
       each if Date.QuarterOfYear([WONeededBy]) = Date.QuarterOfYear(#date(fParameters("Parameters",4),fParameters("Parameters",3),1)) 
       and [Completed] < [WONeededBy] then 1 else 0)


    There are 4 other columns that use very similar versions of the above formulas and are not giving errors.

    Thanks in advance for the assistance.

    • Edited by davidjay43 Friday, September 6, 2019 2:47 PM added info
    Thursday, September 5, 2019 7:20 PM

Answers

  • Hi David. Would it be possible for you to create a small sample file that reproduces the issue and share it here on the forum (e.g. via a OneDrive link)?

    Ehren

    Monday, September 9, 2019 6:42 PM