Forum Discussion

Regular Visitor

1 year ago

Solved

Text.Combine with multiple each if statements

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?

Need Help

Anonymous
1 year ago
You might be able to just remove all but the first "each".

--Nate

Omid_Motamedise

1 year ago

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

1 year ago

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.

Text.Combine with multiple each if statements.pbix29 KB

4 Replies

Omid_Motamedise

Super User

1 year ago

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
Not applicable
1 year ago
You might be able to just remove all but the first "each".

--Nate

Anonymous

Not applicable

1 year ago

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.

Text.Combine with multiple each if statements.pbix29 KB

slorin

Super User

1 year ago

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

Forum Discussion

Text.Combine with multiple each if statements

4 Replies

Related content

Group By - Text.Combine

Multiple IF statements in DAX

IF Then else Statement with Text.Combine

Multiple nested IF statements

Multiple IF statements