Record.AddField( [CustomerID = 1, Name = "Bob", Phone = "123-4567"] , "Address", "123 Main St.")Any further advice you can provide would be greatly appreciated.
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.
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 record
Solved! Go to Solution.
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.
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.
Sorry, I meant to add that I've taken your advice and made sure the field names and the references to their values different now.
Hi MarkLaf,
thank you for your response. Power Query does accept that change, but when I call the function:
Table.AddColumn(#"Removed Other Columns", "DOBProcessing", each #"fBirthdayReport (2)"([DOB]))
expand the record, all of the columns error
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression? Am I completely on the wrong track?
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.
Thank you for the advice and the help MarkLaf. I really appreciate it.
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |