Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Error refreshing a table: Cannot apply operator...to types number and null

I can't see to clear this error I checked all data types and ensured the monetary values are set to decimal number. There are null values in the dataset but not sure why it doesn't like that? 

 

Can anyone explain this? I'm not sure how to expand out the error as it appears truncated? 

 

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Actually the issue is 1 step earlier where you add a column with all dates from start_date through end_date.

    If either of these dates is null, then it won't work.

    My best guess is that end_date's are missing.
    My suggestion would be to use todays date if you have no end date (or something else you can think of), something like:

     

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each { Number.From([start_date])..Number.From(if [end_date] = null then DateTime.Date(DateTime.LocalNow()) else [end_date]) }),

     

    Remark: I adjusted the code out of my head, hopefully it's OK.

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Can you post the text of your query? Need to see what operation you are doing on that. Looks like you are trying to use a text operator or something.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Is this what you need?

       

      let
      Source = Excel.Workbook(File.Contents("T:\RawBIData.xlsx"), null, true),
      Bookings_Sheet = Source{[Item="Bookings",Kind="Sheet"]}[Data],
      #"Promoted Headers" = Table.PromoteHeaders(Bookings_Sheet, [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"start_date", type date}, {"end_date", type date}, {"outcome", type text}, {"rate1_payment", type number}, {"rate1_invoice", type number}, {"days_per_week", Int64.Type}, {"event_ref", Int64.Type}, {"opportunity_ref", Int64.Type}, {"organisation_ref", Int64.Type}, {"Candidate Ref", Int64.Type}, {"T/S Approver", Int64.Type}}),
      #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each { Number.From([start_date])..Number.From([end_date]) }),
      #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
      #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
      #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Contract Days"}}),
      #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Con1 daily Margin", type number}, {"Cons 1 ", Int64.Type}, {"Cons 2", Int64.Type}, {"Cons2 daily margin", type number}, {"Daily Margin", type number}})
      in
      #"Changed Type2"

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        9 years ago

        OK, first, I think we need to narrow down where the error is occurring. What I would do would be to comment out individual lines until you generate get rid of the error, so something like this:

         

         

        let
Source = Excel.Workbook(File.Contents("T:\RawBIData.xlsx"), null, true),
Bookings_Sheet = Source{[Item="Bookings",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Bookings_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"start_date", type date}, {"end_date", type date}, {"outcome", type text}, {"rate1_payment", type number}, {"rate1_invoice", type number}, {"days_per_week", Int64.Type}, {"event_ref", Int64.Type}, {"opportunity_ref", Int64.Type}, {"organisation_ref", Int64.Type}, {"Candidate Ref", Int64.Type}, {"T/S Approver", Int64.Type}})
//#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each { Number.From([start_date])..Number.From([end_date]) }),
//#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
//#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
//#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Contract Days"}}),
//#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Con1 daily Margin", type number}, {"Cons 1 ", Int64.Type}, {"Cons 2", Int64.Type}, {"Cons2 daily margin", type number}, {"Daily Margin", type number}})
in
#"Changed Type"

        The trick to this trouble shooting is 3 steps:

        1. Add a // in front of the lines that you want to comment out
        2. For the last line that is not commented out, get rid of the comma (,) at the end of the line
        3. Change the line after "in" to be the name of the last uncommented line

        Now do a refresh and see if you get the error. If not, then do this:

        1. Uncomment the next line down
        2. Get rid of the comma after this line
        3. Put the comma back in on the line above
        4. Change the line after "in" to be the name of the last uncommented line

        Rinse and repeat these steps until you generate the error. This will narrow down exactly what line is causing the issue.