I'm sorry to have to ask, but it's been breaking my brain and I really haven't found many examples for adding fields and values to a record. The function below appears to be working perfectly well, except that it only returns 'false' which I suppose is coming from the NextMonth field value. Honestly, it's been doing my head in. Any help would be very much appreciated.
(dateDOB as date) =>
let
Birth_Day = Text.From(Date.Day([dateDOB])),
Birth_Month = Text.From(Date.Month([dateDOB])),
LeapBaby =
(if Birth_Day = "29" and Birth_Month = "2"
then "true"
else "false"),
NextBirthday =
(if LeapBaby = "false"
then Text.From([Birth_Day] & "/" & [Birth_Month] & "/" & Text.From(Date.Year(DateTime.LocalNow())))
else "28/2/" & Text.From(Date.Year(DateTime.LocalNow()))),
Age= Number.RoundTowardZero(
Duration.TotalDays(
Duration.From(
Date.FromText([NextBirthday])-[dateDOB])
)/365.25
),
BirthDayMessage =
(if LeapBaby = true and NextBirthday = "28/2/2018"
then "Happy Leap Year Birthday!"
else "Happy"),
NextMonth = Date.IsInNextMonth(Date.FromText(NextBirthday)),
record = Record.AddField = ([
Message = BirthDayMessage, Age = Age, NextMonth = NextMonth, NextBirthday = NextBirthday
])
in
recordChange last line record to:
record = [Message = BirthDayMessage, Age = Age, NextMonth = NextMonth, NextBirthday = NextBirthday]
Also, for readability, I would avoid making field names exactly the same as the reference to their values, and instead would recommend something like 'Age = AgeVal, NextMonth = NexMonthVal', etc.
I don't think it's an issue here, but in other situations having different things with the same name can cause an error.
Yes, taking another look at your function, all your references have brakets around them. Remove the brackets and that will help. You may need to further troubleshoot, though. Generally when creating a complex function, it's worth using dummy data to validate it's working.
E.g. Change the top of your M to the following and work through all errors, then try to call function:
/** (dateDOB as date) => **/
let
dateDOB = #date(2001,2,28),
Birth_Day = Text.From(Date.Day(dateDOB)),
...
...
...(I commented out the parameter at top and replaced with a constant to test out, and removed brackets on the reference in the first line to give you an example).
Once your function works for the test value, just remove the dateDOB line under let, and remove the comment syntax (/** & **/) around your parameters line.
Hi Anonymous
Here are some articles for your reference.
http://radacad.com/power-query-function-that-returns-multiple-values
https://blog.crossjoin.co.uk/2017/01/10/record-addfield-functions-and-the-delayed-option-in-m/
Regards,
Cherie
Hi Cherie,
thanks for taking the time to respond to my question. I've been using the RADCAD article to create the query, but it doesn't explain how to add multiple columns to the record. For example, the line below, as I understand it, creates the record called 'record', adds a column called First Date of the Month and sets the value for that column.
record=Record.AddField([],"First Date of Month",FirstDate),
The next line assigns record to result set and adds another column, and a value for that column.
resultset=Record.AddField(record,"Last Date of Month",LastDate)
Record.AddField( [CustomerID = 1, Name = "Bob", Phone = "123-4567"] , "Address", "123 Main St.")Any further advice you can provide would be greatly appreciated.
Change last line record to:
record = [Message = BirthDayMessage, Age = Age, NextMonth = NextMonth, NextBirthday = NextBirthday]
Also, for readability, I would avoid making field names exactly the same as the reference to their values, and instead would recommend something like 'Age = AgeVal, NextMonth = NexMonthVal', etc.
I don't think it's an issue here, but in other situations having different things with the same name can cause an error.
