Forum Discussion

harirao's avatar
harirao
Post Prodigy
6 years ago
Solved

How to create columns using M language

Hi All,

Can you please assist how to create columns in M language.

Below are three columns created 

Last Refresh Date = Query1[reportdate]
ReportingDate = IF(Query1[reportdate]=MAX(Query1[reportdate]),"CurrentWeek",FORMAT(Query1[reportdate],"YYYY-MM-DD"))
Visibility Group = IF(Query1[actual_visibility_Weeks]=0, "No Visibility",IF(AND(Query1[actual_visibility_Weeks]>0,Query1[actual_visibility_Weeks]<=5), "<6 weeks Visibility", ">6 weeks Visibility"))

Thank you

Regards,
Hari 
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

     

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Assume you have the following in an excel sheet and it is imported as Query1

     

    ReportDateactual_visibility_weeks
    01-07-20208
    02-07-20203
    03-07-20208
    04-07-20202
    05-07-20209
    06-07-20200
    07-07-20203
    08-07-20204
    09-07-202010
    10-07-202010
    11-07-20205
    12-07-20207
    13-07-20206
    14-07-20207
    15-07-20209
    16-07-20208
    17-07-20208
    18-07-20201
    19-07-20206
    20-07-20201
    21-07-20203

     

    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.

    • harirao's avatar
      harirao
      Post Prodigy
      6 years ago

      Hello 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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.