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,
I have some demo sales data in a model which is quite old. I'd like to use PowerQuery to add two years to a date column.
So I added a Transform Get Date step and looked at the syntax, hoping to replace the Date.Year function with Date.AddYears and make the neccessary changes in the formula bar
So I checked the M reference for Date.Year and it says the syntax is...
Date.Year(dateTime as datetime) as nullable number
However in the Formula Bar and the Advanced editor this syntax doesn't appear. There is no (...) after the Date.Year. Instead I have
=Table.TransformColumns(#"Filtered Rows",{{"MyDate", Date.Year, Int64.Type}})
Now I'm stuck.
I was expecting something like
Table.TransformColumns(#"Filtered Rows",{{"MyDate", Date.Year("MyDate"), Int64.Type}})
and hoped I could just replace with
Table.TransformColumns(#"Filtered Rows",{{"MyDate", Date.AddYears("MyDate,2), Int64.Type}})
I've looked everywhere for some guides on syntax here as it differs from the Microsoft guide, but can't see find anything. Also I can't really see how a columnis referenced in an M function... do I use [MyField] or "Myfield" or {Myfield}
Happy to read up but I can't see where to start...
Thanks in advance!
Solved! Go to Solution.
Hi @robstv
To answer the immediate question, the syntax to add two years rather than convert to the year would be:
=Table.TransformColumns(#"Filtered Rows",{{"MyDate", each Date.AddYears(_,2), type date}})
For Table.TransformColumns, the first item in each "transformation list" (i.e. the three items within the inner curly braces) is the name of the column to be transformed, provided as text.
The second item in each "transformation list" has to be a function that takes a single argument. The original value in the column is transformed using this function.
For example, the functions are highlighted in red in the "transformation lists" below:
{"MyDate", Date.Year, Int64.Type}
{"MyDate", each Date.AddYears(_,2), type date}
Functions that need to be told which column to act upon normally take the column name as text. For example Table.TransformColumns or Table.CombineColumns.
However, when you have a table (or record), and you want to refer to a column of the table (or field of the record) as an object, you can use square brackets (the "lookup operator") to refer to the column (or field) by name, for example TableName[ColumnName] or RecordName[FieldName].
For me, trial and error and observing code created by the interface is a good way to learn, but all the answers should be under here somewhere:
https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference
In particular
https://docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification
Regards,
Owen
Hi @robstv
To answer the immediate question, the syntax to add two years rather than convert to the year would be:
=Table.TransformColumns(#"Filtered Rows",{{"MyDate", each Date.AddYears(_,2), type date}})
For Table.TransformColumns, the first item in each "transformation list" (i.e. the three items within the inner curly braces) is the name of the column to be transformed, provided as text.
The second item in each "transformation list" has to be a function that takes a single argument. The original value in the column is transformed using this function.
For example, the functions are highlighted in red in the "transformation lists" below:
{"MyDate", Date.Year, Int64.Type}
{"MyDate", each Date.AddYears(_,2), type date}
Functions that need to be told which column to act upon normally take the column name as text. For example Table.TransformColumns or Table.CombineColumns.
However, when you have a table (or record), and you want to refer to a column of the table (or field of the record) as an object, you can use square brackets (the "lookup operator") to refer to the column (or field) by name, for example TableName[ColumnName] or RecordName[FieldName].
For me, trial and error and observing code created by the interface is a good way to learn, but all the answers should be under here somewhere:
https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference
In particular
https://docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification
Regards,
Owen
Thanks Owen, very helpful!
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |