Forum Discussion
Error refreshing a table: Cannot apply operator...to types number and null
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.
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"
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:
- Add a // in front of the lines that you want to comment out
- For the last line that is not commented out, get rid of the comma (,) at the end of the line
- 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:
- Uncomment the next line down
- Get rid of the comma after this line
- Put the comma back in on the line above
- 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.
Found the line but it's the most critical of all the transformation steps. I have a table with a start and end of a contract and a daily margin associated. I needed to show the daily margin for all dates between the start and end so expanded the columns
this step pulls out all the dates between start and end and list the all important daily margin accordingly
I don't know how to achieve this another way but all ears?
Thanks.
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.
Hi,
I have a similar issue. I've have a column with start dates and a column with end dates. Both formatted as Date. No nulls in either of the columns.
I use this code:
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Dates", each List.Transform({Number.From([Fromdate])..Number.From([ToDate])}, each Date.From(_)))And receive the following error when I apply the query changes. No errors when editing the query.
What am i doing wrong?