Text.Combine with multiple each if statements

[AnalystDiogenes]

I have a bunch of tables without any keys in them, and I want to create the same key for each combination of certain columns. I also need to make this straightforward for someone else to do when adding a new table that has the same columns.

So I have [CAH level subject], [Level of Study], [Mode of study] and [Academic year]. (All text columns).
Simply concatinating them all would be a really big field, so I want to use standard abbreviations when I create this key.

In the case of CAH level subject, and Academic Year, I can just extract particular characters from the text before the relevant delimiter to get all the information I need. With Level of Study, I want to turn a long description into a two or three character abbreviation.

Here's what I came up with:

= Table.AddColumn(#"Changed Type1", "Key",

each Text.Combine({

Text.BeforeDelimiter([CAH level subject], " "),

":",

each if [Mode of study] = "Full-time" then "FT"

else if [Mode of study] = "Part-time" then "PT"

else null,

":",

each 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)

This doesn't work, because ethe each if commands are functions (albeit ones that return text strings), and the Text.Combine wants it's arguments to be text, not functions.

Is there a way of having one function do this, so that the future person adding tables can just copy and past the formula when adding a column to new tables?

[Anonymous · Accepted solution]

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

--Nate

[Omid_Motamedise · Accepted solution]

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
)

[slorin]

Hi, @AnalystDiogenes 

Another solution with 2 records and Record.FieldOrDefault

Mode_of_study = Record.FromList({"FT", "PT"}, {"Full-time","Part-time"})

Level_of_study = Record.FromList({"PRG", "PGT", "1st", "oUG"}, {"Postgraduate (research)", "Postgraduate (taught)", "First degree", "Other undergraduate"})

 then add column

Text.Combine({
  Text.BeforeDelimiter([CAH level subject], " "), ":", 
  Record.FieldOrDefault(Mode_of_study, [Mode of study]), ":",
  Record.FieldOrDefault(Level_of_study, [Level of study]), ":",
  Text.BeforeDelimiter([Academic year], "/")
})

 Stéphane

[Anonymous · Accepted solution]

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.

[Omid_Motamedise · Accepted solution]

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
)

[Anonymous · Accepted solution]

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

--Nate