First of all, a big shout out to the truly BRILLIANT Power Query course Mynda has done. It has saved me SO MUCH time and I'm enjoying getting to grips with M (ooh, makes me think I'm James Bond...better stop now...).
I have created a Custom column and here is the code:
if [#"Deferred %"]=1 or [#"Deferred %"]=0 or [In Contract Date]="No" then 0 else if [#"S&M Contract Start date"]=[Revenue Date] then [Amount]*1-[#"Deferred %"] + [Amount]/[Revenue Days] else [Amount]/[Revenue Days]
When I created it, it said "no syntax errors" but when I tried to run it, I got this message..
Expression. Error: We cannot apply operator / to types Number and Duration
Details:
Operator =/
Left =27000
Right=365.00:00:00
"Revenue Days" is the difference between a Start Date and an End Date. That has been formatted as a whole number.
I have formatted both Start Date and End Date as Date. I reckon it's something simple that I am missing. Thank you.
Hi Anne,
Great to hear you're reaping huge rewards with Power Query. Spread the word. It's surprising how many people still aren't using it.
Now, on to your error. It seems like Revenue Days is still a Duration type, or at least being interpreted as such. When I add a column to subtract a Start Date from an End Date I get this line of code:
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each Duration.Days([End] - [Start]), Int64.Type)
You can see the data type is Int64.Type
Is that what you get?
Mynda
That's what I am getting. How should I rectify it? Thanks. I'm now tantalisingly close to getting this project over the line. Planning to finish it off with an icing of parameter queries (as you do! 🙂
Hi Ann,
That should give you the correct results. He's my sample file that works. It might help you troubleshoot.
If not, please send your Excel file so we can see what's going on.
Mynda
hi Mynda
I got all that bit working - I basically rolled it back, and did the date re-formatting at the beginning. So that's working 🙂 Thank you 🙂
Now I have another dilemma. I have set up a parameter query that references a date as per your excel-lent video 🙂 . Basically the user will input this date. All income earned before this date is called "Not deferred", all income after it is "Deferred". I set up a column called Deferred Income?
= Table.AddColumn(#"Added Custom5", "Deferred Income?", each if [Revenue Date]<=Parameter_Deferred_Date then "Not Deferred" else "Deferred")
However I get the error
Expression.Error: We cannot apply operator < to types DateTime and Date.
Details:
Operator=<
Left=30/06/2018 00:00:00
Right=01/01/2013
Again, probably a simple thing...but what am I missing? Thanks
Hi Anne
As you guessed already, it's a simple error, hope the colors will help you see what the text already tells you:
We cannot apply operator < to types DateTime and Date.
[Revenue Date]<=Parameter_Deferred_Date
Left=30/06/2018 00:00:00 Right=01/01/2013
You have to change Revenue Date to Date type, OR Parameter to DateTime type. It does not matter which one you change, both should have the same data type.
Excellent - again, I went back and fixed this and it worked...sigh, it's always the simple things...
Hi Keith,
It's not clear where the error is occurring, but presumably it's the "Filtered Rows3" step. The error is telling you that you're trying to reference a whole table instead of a record when performing your logical test.
Please start a new topic and attach your file or a small sample file that illustrates what you're trying to do.
Mynda
