locked
Power Query M functions with no parameters? RRS feed

  • Question

  • I want to create PQ statements using the UI and edit them. As an example, I can transform a date column into a day of the week (int) using the UI. This M code is generated:

    = Table.TransformColumns(#"Changed Type with Locale1",{"Account_Created", Date.DayOfWeek})

    Per the support docs, DayofWeek function takes as parameters: col name (eg Account_Created) and an optional int which is the start day. Default is 0 for Sunday etc. The problem is that the UI code created above is a list (in angle brackets) with the col name first and the function name second.  Nowhere in the online documentation is it explained how to edit this syntax to change the default day parameter from 0. 

    I can fix the problem an alternate way by manually typing what i want in an Add Column/Custom Column dialog box:

    e.g. 

    = Date.DayOfWeek([Transaction_Date],1)

    But it would be nice if there was some explanation of how to edit the Date.DayOfWeek function inside the TransformColumns statement at the top. The current documentation for TransformColumns provides examples which are ridiculously trivial and completely uninformative

    Sunday, October 7, 2018 7:20 PM

Answers

  • In an ideal world, every step would have its own gear icon to the right of it so you can easily modify it but, as you may have found out, some transformations don't have that. I'd highly suggest that you post this idea on the official Power BI ideas site so it can be implemented at some point inside of Power Query.

    For your specific example, if you wanted to modify the step that uses the Table.TransformColumns function, you'd come up with the following formula:

    Table.TransformColumns(#"Changed Type with Locale1",{{"Account_Created", each Date.DayOfWeek(_,1), Int64.Type}})

    of course, creating a custom column would be far easier, but this is how you'd do it if you felt comfortable editing the full M code of a step.

    Sunday, October 7, 2018 8:00 PM
  • Also note that Date.DayOfWeek's first parameter is not a column name, but a date value (which in your example happens to come from a particular column). Table.TransformColumns is passing each value contained in the specified column (Account_Created in your case) to the specified function (Date.DayOfWeek above). As Miguel suggested, you can wrap the call to Date.DayOfWeek in another function, which allows you to pass the optional parameter to Date.DayOfWeek.

    Rewriting Miguel's example with a slightly different syntax might make what's going on a bit clearer:

    Table.TransformColumns(#"Changed Type with Locale1", {{"Account_Created", (acctCreatedValue) => Date.DayOfWeek(acctCreatedValue, 1), Int64.Type}})

    Hope this helps.
    Ehren

    Thursday, October 11, 2018 6:12 PM

All replies

  • In an ideal world, every step would have its own gear icon to the right of it so you can easily modify it but, as you may have found out, some transformations don't have that. I'd highly suggest that you post this idea on the official Power BI ideas site so it can be implemented at some point inside of Power Query.

    For your specific example, if you wanted to modify the step that uses the Table.TransformColumns function, you'd come up with the following formula:

    Table.TransformColumns(#"Changed Type with Locale1",{{"Account_Created", each Date.DayOfWeek(_,1), Int64.Type}})

    of course, creating a custom column would be far easier, but this is how you'd do it if you felt comfortable editing the full M code of a step.

    Sunday, October 7, 2018 8:00 PM
  • Also note that Date.DayOfWeek's first parameter is not a column name, but a date value (which in your example happens to come from a particular column). Table.TransformColumns is passing each value contained in the specified column (Account_Created in your case) to the specified function (Date.DayOfWeek above). As Miguel suggested, you can wrap the call to Date.DayOfWeek in another function, which allows you to pass the optional parameter to Date.DayOfWeek.

    Rewriting Miguel's example with a slightly different syntax might make what's going on a bit clearer:

    Table.TransformColumns(#"Changed Type with Locale1", {{"Account_Created", (acctCreatedValue) => Date.DayOfWeek(acctCreatedValue, 1), Int64.Type}})

    Hope this helps.
    Ehren

    Thursday, October 11, 2018 6:12 PM