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

[Anonymous]

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? 

[Greg_Deckler]

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]

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]

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.

[Anonymous]

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.

[MarcelBeug · Accepted solution]

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.

[jakalfayan]

This didn't fix it. Please help

[Anonymous]

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?

[jakalfayan]

I'm pulling my data via an API query to our CRM and I'm getting this exact same ERROR even when I create a new column for return date that has the if/then statement in it using Date.From(DateTime.LocalNow())

CAN SOMEONE HELP PLEASE???

[jakalfayan]

CAN SOMEONE HELP PLEASE???