Function to return multiple values as record

[Anonymous]

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

[MarkLaf · Accepted 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.

[v-cherch-msft]

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

[Anonymous]

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)

I'm not sure how to add more columns and their respective values to the record.  The language specifications says you can use it as follows, but when I try that, it errors:

Record.AddField( [CustomerID = 1, Name = "Bob", Phone = "123-4567"] , "Address", "123 Main St.")  

Any further advice you can provide would be greatly appreciated.

Regards

Niko

[MarkLaf · Accepted 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.

[Anonymous]

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.

[Anonymous]

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?

[MarkLaf · Accepted solution]

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.

[Anonymous]

Thank you for the advice and the help MarkLaf.  I really appreciate it.