Text.Combine with multiple each if statements

You might be able to just remove all but the first "each".

--Nate

rewrite it as below

Table.AddColumn(
  #"Changed Type1", 
  "Key", 
  each Text.Combine(
    {
      Text.BeforeDelimiter([CAH level subject], " "), 
      ":", 
      if [Mode of study] = "Full-time" then
        "FT"
      else if [Mode of study] = "Part-time" then
        "PT"
      else
        null, 
      ":", 
      if [Level of study] = "Postgraduate (research)" then
        "PGR"
      else if [Level of study] = "Postgraduate (taught)" then
        "PGT"
      else if [Level of study] = "First degree" then
        "1st"
      else if [Level of study] = "Other undergraduate" then
        "oUG"
      else
        null, 
      ":", 
      Text.BeforeDelimiter([Academic Year], "/")
    }
  ), 
  type text
)

Hi AnalystDiogenes ,

Please try this:

Text.Combine(
    {
        Text.BeforeDelimiter([CAH level subject], " "), ":",
        if [Mode of study] = "Full-time" then "FT" 
        else if [Mode of study] = "Part-time" then "PT" 
        else null, ":",
        if [Level of study] = "Postgraduate (research)" then "PGR" 
        else if [Level of study] = "Postgraduate (taught)" then "PGT" 
        else if [Level of study] = "First degree" then "1st" 
        else if [Level of study] = "Other undergraduate" then "oUG" 
        else null, ":",
        Text.BeforeDelimiter([Academic year], "/")
    }
)

And the final output is as below:

Here is the whole M code in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY3LCsIwEEV/ZZiVhYo2zQ/4oFt1HbIYzJAUaoXJ5P9tC1Yo7i7cczjO4QkarLErw7DX/sXTvr+zRqFQSBl2wplJnqmaHnM09vBo0NcOz2BmlkT/e0olJv1ZZrEu0G5qXS9ZIXAU5hVuF/gKdpO4aWKBMgaWb2l1LHr/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CAH level subject" = _t, #"Mode of study" = _t, #"Level of study" = _t, #"Academic year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAH level subject", type text}, {"Mode of study", type text}, {"Level of study", type text}, {"Academic year", type text}}),
    AddColumn = Table.AddColumn(#"Changed Type", "Key", each Text.Combine(
    {
        Text.BeforeDelimiter([CAH level subject], " "), ":",
        if [Mode of study] = "Full-time" then "FT" 
        else if [Mode of study] = "Part-time" then "PT" 
        else null, ":",
        if [Level of study] = "Postgraduate (research)" then "PGR" 
        else if [Level of study] = "Postgraduate (taught)" then "PGT" 
        else if [Level of study] = "First degree" then "1st" 
        else if [Level of study] = "Other undergraduate" then "oUG" 
        else null, ":",
        Text.BeforeDelimiter([Academic year], "/")
    }
))
in
    AddColumn

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.