Forum Discussion
Post ProdigyHow to create columns using M language
Yes. Only thing you have to take care of the tables names and field names that we are passing to these functions. You can add the following codes before the "IN" line and change the #"Changed Type" in the 2nd line below to #"Sorted Rows" because that is the step name that you are referring to the table in your code. As I mentioned earlier, please correct the field names correctly. for example, I have used field names like [ReportDate],[actual_visibility_weeks] etc... If the names are different in your table, change them accordingly, but keep the square brackets.
AddedLastRefreshDate =Table.AddColumn( #"Changed Type", "Last Refresh Date", each [ReportDate] ), AddedReportingDate = Table.AddColumn( AddedLastRefreshDate, "Reporting Date", each if [ReportDate] = List.Max(Table.Column(AddedLastRefreshDate,"ReportDate")) then "CurrentWeek" else Date.ToText([ReportDate],"YYYY-MM-DD") ), AddedVisibilityGroup = Table.AddColumn( AddedReportingDate, "Visibility Group", each if [actual_visibility_weeks] >0 and [actual_visibility_weeks] <=5 then "<6 weeks visibility" else ">6 weeks visibility" )These are actually simple in-built functions.
Table.AddColumn(<tableName>,<NewColumnName>,each <calculation>)
IF <condition> then <statement> else <statement>
List.Max() returns the maximum from a list.
Table.Column() converts a table's column into a list which we can pass it on to List.Max()
Date.ToText() converts a date value to text.
Assume you have the following in an excel sheet and it is imported as Query1
| ReportDate | actual_visibility_weeks |
| 01-07-2020 | 8 |
| 02-07-2020 | 3 |
| 03-07-2020 | 8 |
| 04-07-2020 | 2 |
| 05-07-2020 | 9 |
| 06-07-2020 | 0 |
| 07-07-2020 | 3 |
| 08-07-2020 | 4 |
| 09-07-2020 | 10 |
| 10-07-2020 | 10 |
| 11-07-2020 | 5 |
| 12-07-2020 | 7 |
| 13-07-2020 | 6 |
| 14-07-2020 | 7 |
| 15-07-2020 | 9 |
| 16-07-2020 | 8 |
| 17-07-2020 | 8 |
| 18-07-2020 | 1 |
| 19-07-2020 | 6 |
| 20-07-2020 | 1 |
| 21-07-2020 | 3 |
When you import the excel sheet (or whatever source) the following power query (similar to it) will be generated automatically.
let
Source = Excel.Workbook(File.Contents("C:\Documents\Query1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ReportDate", type date}, {"actual_visibility_weeks", Int64.Type}}),
in
#"Changed Type"
You can add your three columns by adding 3 more steps of code-snippets before the "in" line and changing the #"Changed Type" name to your last step name. The modified query will look like this.
let
Source = Excel.Workbook(File.Contents("C:\Documents\Query1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ReportDate", type date}, {"actual_visibility_weeks", Int64.Type}}),
AddedLastRefreshDate =Table.AddColumn(
#"Changed Type",
"Last Refresh Date", each [ReportDate]
),
AddedReportingDate =
Table.AddColumn(
AddedLastRefreshDate,
"Reporting Date",
each if [ReportDate] = List.Max(Table.Column(AddedLastRefreshDate,"ReportDate")) then "CurrentWeek" else Date.ToText([ReportDate],"YYYY-MM-DD")
),
AddedVisibilityGroup =
Table.AddColumn(
AddedReportingDate,
"Visibility Group",
each if [actual_visibility_weeks] >0 and [actual_visibility_weeks] <=5 then "<6 weeks visibility" else ">6 weeks visibility"
)
in
AddedVisibilityGroup
The result will look like this (Notice the step names on the right which will be added based on your m language code.
Hope it helps.
Post ProdigyHello Anonymous
Thanks for your response, i have connected to sql db and created import query, still i will be able to include below code in m language?
Thank you
Regards,
Yes. Only thing you have to take care of the tables names and field names that we are passing to these functions. You can add the following codes before the "IN" line and change the #"Changed Type" in the 2nd line below to #"Sorted Rows" because that is the step name that you are referring to the table in your code. As I mentioned earlier, please correct the field names correctly. for example, I have used field names like [ReportDate],[actual_visibility_weeks] etc... If the names are different in your table, change them accordingly, but keep the square brackets.
AddedLastRefreshDate =Table.AddColumn( #"Changed Type", "Last Refresh Date", each [ReportDate] ), AddedReportingDate = Table.AddColumn( AddedLastRefreshDate, "Reporting Date", each if [ReportDate] = List.Max(Table.Column(AddedLastRefreshDate,"ReportDate")) then "CurrentWeek" else Date.ToText([ReportDate],"YYYY-MM-DD") ), AddedVisibilityGroup = Table.AddColumn( AddedReportingDate, "Visibility Group", each if [actual_visibility_weeks] >0 and [actual_visibility_weeks] <=5 then "<6 weeks visibility" else ">6 weeks visibility" )These are actually simple in-built functions.
Table.AddColumn(<tableName>,<NewColumnName>,each <calculation>)
IF <condition> then <statement> else <statement>
List.Max() returns the maximum from a list.
Table.Column() converts a table's column into a list which we can pass it on to List.Max()
Date.ToText() converts a date value to text.