Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Apologies for the inexperience. I'm sure this is a super trivial and easy fix but I did some power query training a couple of years ago in my previous company and found it massively useful. I had no issues creating custom columns using various functions, but all of a sudden, now I've joined a new organisation, I'm trying to do some basic date calculations in a custom column and it just returns a 'Function' value as below. I'm pretty sure this is likely just a setup issue but for the life of me I can't find the fix online. Does anyone know how to fix this? I really don't understand why this isn't displaying the current DateTime.
Thanks in advance.
Solved! Go to Solution.
Can you please include the code you are using and what you expect it to return?
It will likely be easy to fix but there's not enough info.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi KNP. Thanks for taking the time to get back to me.
That code at the moment is just 'DateTime.LocalNow'.
I wanted to create a formula which gives me the difference in days between a start and end date. However, after writing out the code, this error flashed up so I started deleting parts of the code until I figured out what was causing the problem.
Hi @Mknott93,
See below gif.
Here is the complete code. Paste this into the advanced editor of a blank query.
let
Source = #date(2021, 11, 16),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "StartDate"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "EndDate", each #date(2021, 11, 18)),
#"Changed Type" = Table.TransformColumnTypes(
#"Added Custom",
{{"StartDate", type date}, {"EndDate", type date}}
),
#"Inserted Date Subtraction" = Table.AddColumn(
#"Changed Type",
"Subtraction",
each Duration.Days([EndDate] - [StartDate]),
Int64.Type
)
in
#"Inserted Date Subtraction"
I hope this helps.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Thanks for your help. I actually think I was just missing the brackets in my formula which is embarrasing, but this is a very useful code for when I need to do the date subtraction. Thanks again.
You're welcome.
You described the problem in your reply as wanting the difference between two dates, which is what I provided.
Also, be careful with the DateTime.LocalNow() depending on your query structure it can change during successive calls in the same refresh, producing unexpected results.
Typically you'd be better off with DateTime.FixedLocalNow().
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Just change it to:
=DateTime.LocalNow()
Wow, that's a little embarrasing. I've done this so many times, no idea how I missed that. Thanks for this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |