Split columns?

[edithees]

Hi everyone, if I have 1 column that contains course codes e.g. F2001 where 20 means 2020 and 01 means first term but I want to add a corresponding year in a new column next to it so what should I do? I have a number of course codes in this column F2101,F2102,F2001,F2002,F1801,F1802,F1901,F1902 (so the first 2 digit refers to the year and last two is the term so Term 1 or Term 2). The year column is not in my table but I can manually add it (which is super quick) but I want to see how it could be done in power query. I want the year column to contain Term 1 2018, Term 2 2018..or just the year. Thankyou!

[KNP · Accepted solution]

Paste this into the advanced editor of a blank query...

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WcjMyNDDUAZFGQNIAzDYAsQ0tQGwgCWJbgtmWBkZKsbEA", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}),
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Changed Type",
    "Column1",
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
    {
      "Column1.1",
      "Column1.2",
      "Column1.3",
      "Column1.4",
      "Column1.5",
      "Column1.6",
      "Column1.7",
      "Column1.8"
    }
  ),
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Split Column by Delimiter",
    {
      {"Column1.1", type text},
      {"Column1.2", type text},
      {"Column1.3", type text},
      {"Column1.4", type text},
      {"Column1.5", type text},
      {"Column1.6", type text},
      {"Column1.7", type text},
      {"Column1.8", type text}
    }
  ),
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
  #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns", {"Attribute"}),
  #"Added Custom Column" = Table.AddColumn(
    #"Removed Columns",
    "Custom",
    each Text.Combine({"Term ", Text.Middle([Value], 4), " 20", Text.Middle([Value], 1, 2)}),
    type text
  )
in
  #"Added Custom Column"

Let me know if you need more assistance.

[BA_Pete]

Hi @edithees ,

@KNP has given you the solution for your request. However, I would strongly recommend splitting your terms and years into separate columns. This will provide far more reporting flexibility against both term and year independently.

Adding onto the end of @KNP 's code, you would create these separately as new custom columns:

// Year
Text.Combine({"20", Text.Middle([Value], 1, 2)})

// Term
Text.Combine({"Term ", Text.Middle([Value], 4)})

Just my tuppence worth 🙂

Pete