Define a temporary function or multiple variables in a single column

[v-henryk-mstf]

In this article, we will talk about how to get the expected outputs via DAX or M(in Power Query) with different methods. It will be a simple sample to know how to define variables in both languages.

Sample Data:

Scenario:
Suppose I would like to calculate how many different programs each employee has gone through.

• If the employee only has one program, then we need to point out the specific program.
• If the employee has taken part in more than one program, then calculate the number of different programs.

The tricky thing is that the target program name is included in the Program ID column. And how could we apply different conditions in a single column?

Method using DAX:

1. Firstly, add a new column named Program Type to get the first string by using LEFT()
2. Get the distinct count of Program Type of each Employee ID. Then if the distinct count =1, it means the Program Type is unique , so we could use MAX() or MIN() to find specific type name of each Employee ID. Otherwise if distinct count >1, then just return it.
3. In addition, since the two conditions returns different types——one is number, the other is string, in such a case, the syntax will return errors like “Expressions that yield variant data-type cannot be used to define calculated columns”. So please use CONVERT() to change number type to string.

Program Type = LEFT([Program ID],1)

Result =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Program Type] ),
        ALLEXCEPT ( 'Table', 'Table'[Employee ID] )
    )
RETURN
    IF (
        _count = 1,
        CALCULATE (
            MAX ( 'Table'[Program Type] ),
            ALLEXCEPT ( 'Table', 'Table'[Program Type] )
        ),
        CONVERT ( _count, STRING )
    )

Output:

Methods using M in Power Query:

Method1: normal

• Get the actual Program type
• Use Group feature to transform data
• Add a custom column: count of distinct Type
• Add another column: It’s based on the previous column
• Then expand necessary columns and remove other columns.

Whole M syntax:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "TYu5DcAwDMR2udqNHj8pk4xhaP81ckhhiY1wBLU3BA1CeG5RQ7QNTfd4H78zDiW183Sn6xxGajc4nFQ3053fle6d60LEBw==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, #"Employee ID" = _t, #"Program ID" = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"ID", Int64.Type}, {"Employee ID", Int64.Type}, {"Program ID", type text}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Program Type", 
    each Text.Start([Program ID], 1)
  ), 
  #"Grouped Rows" = Table.Group(
    #"Added Custom", 
    {"Employee ID"}, 
    {
      {
        "Count", 
        each _, 
        type table [
          ID = nullable number, 
          Employee ID = nullable number, 
          Program ID = nullable text, 
          Program Type = text
        ]
      }
    }
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Grouped Rows", 
    "Custom", 
    each List.Count(List.Distinct(Table.ToList(Table.SelectColumns([Count], "Program Type"))))
  ), 
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom1", 
    "Result", 
    each if [Custom] > 1 then [Custom] else Record.Field([Count]{0}, "Program Type")
  ), 
  #"Expanded Count" = Table.ExpandTableColumn(
    #"Added Custom2", 
    "Count", 
    {"ID", "Program ID"}, 
    {"ID", "Program ID"}
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Expanded Count", {"Custom"})
in
  #"Removed Columns"

Output:

Method2: use variables

• Get the actual Program type
• Use Group feature to transform data
• Then how do we apply different conditions in a single column? Here we need some variables:
  • Expression1: define _count =Count distinct rows
  • Expression2: define f=The first Program typed
  • Condition: if _count >1 then return it else return f
• Then expand columns.

Whole M syntax:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "TYu5DcAwDMR2udqNHj8pk4xhaP81ckhhiY1wBLU3BA1CeG5RQ7QNTfd4H78zDiW183Sn6xxGajc4nFQ3053fle6d60LEBw==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, #"Employee ID" = _t, #"Program ID" = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"ID", Int64.Type}, {"Employee ID", Int64.Type}, {"Program ID", type text}}
  ), 
  #"Added Custom1" = Table.AddColumn(
    #"Changed Type", 
    "Program Type", 
    each Text.Select([Program ID], {"A" .. "Z"})
  ), 
  #"Grouped Rows" = Table.Group(
    #"Added Custom1", 
    {"Employee ID"}, 
    {
      {
        "Count", 
        each _, 
        type table [
          ID = nullable number, 
          Employee ID = nullable number, 
          Program ID = nullable text, 
          Program Type = text
        ]
      }
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows", 
    "Result", 
    each [
      _count = List.Count(List.Distinct(Table.ToList(Table.SelectColumns([Count], "Program Type")))), 
      f  = Table.FirstValue(Table.SelectColumns([Count], "Program Type")), 
      r  = if _count > 1 then _count else f
    ][r]
  ), 
  #"Expanded Count" = Table.ExpandTableColumn(
    #"Added Custom", 
    "Count", 
    {"ID", "Program ID"}, 
    {"ID", "Program ID"}
  )
in
  #"Expanded Count"

Output:

Method3: use a temporary function

• Firstly, use Group feature to transform data
• Almost the same as the above method. But here we will define a temporary function to create a new table.

Whole M syntax:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "TYu5DcAwDMR2udqNHj8pk4xhaP81ckhhiY1wBLU3BA1CeG5RQ7QNTfd4H78zDiW183Sn6xxGajc4nFQ3053fle6d60LEBw==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, #"Employee ID" = _t, #"Program ID" = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"ID", Int64.Type}, {"Employee ID", Int64.Type}, {"Program ID", type text}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Employee ID"}, 
    {{"Count", each _, type table [Group ID = nullable number, Order ID = nullable text]}}
  ), 
  //Function Start
  fxProcess = (Tbl) =>
    let
      #"Added Custom" = Table.AddColumn(Tbl, "Custom", each Text.Start([Program ID], 1)), 
      #"Added Custom1" = Table.AddColumn(
        #"Added Custom", 
        "Result", 
        each [
          _count = List.Count(
            List.Distinct(Table.ToList(Table.SelectColumns(#"Added Custom", "Custom")))
          ), 
          f = Table.FirstValue(Table.SelectColumns(#"Added Custom", "Custom")), 
          r = if _count > 1 then _count else f
        ][r]
      )
    in
      #"Added Custom1", 
  //Function End
  #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Count])), 
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Employee ID", "Count"}), 
  #"Expanded Custom" = Table.ExpandTableColumn(
    #"Removed Columns", 
    "Custom", 
    {"Employee ID", "ID", "Program ID", "Result"}, 
    {"Employee ID", "ID", "Program ID", "Result"}
  )
in
  #"Expanded Custom"

Output:

Hope this article will help people with similar questions.

Author: Eyelyn Qin

Reviewer: Kerry Wang & Ula Huang

Define a temporary function or multiple variables in a single column-Eyelyn Qin.pbix