Forum Discussion

PPalkowski's avatar
PPalkowski
Helper II
6 years ago
Solved

Trying to get decade from year

I am new to power query, couldn't find an easy solution

I am trying to get the decade from the year,  slowly built this

 

Added column: This takes a year and converts it to text

= Table.AddColumn(#"Renamed Columns", "Y1", each Number.ToText ([Year]))

 

Added column: This take the first two characters at this point, 19 or 20

= Table.AddColumn(#"Added Custom", "Y2", each Text.Start([Y1], 2))

 

Added column: takes the 3rd character, and adds an 0s

= Table.AddColumn(#"Added Custom2", "Y4", each [Y2] & Text.At ([Y1], 2)&"0s")

 

The result is 1920s, 1930s, 1940s, etc…

Is there a way to do this in one step?

Need Help
  • solexecution's avatar
    solexecution
    6 years ago

    Hi, try pasting below to your blank Advanced Query editor and see if it does what you are after, line below the comment is I believe what you are after.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlCK1QExDE1hDBMYAyZlYAxmGFpaWkIZFqZQhgmQEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    // Below is all in one line, hope that helps
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Start(Text.From([Year]),3)&"0s")
in
    #"Added Custom"

5 Replies

  • solexecution's avatar
    solexecution
    Frequent Visitor
    6 years ago

    Hi, try pasting below to your blank Advanced Query editor and see if it does what you are after, line below the comment is I believe what you are after.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlCK1QExDE1hDBMYAyZlYAxmGFpaWkIZFqZQhgmQEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    // Below is all in one line, hope that helps
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Start(Text.From([Year]),3)&"0s")
in
    #"Added Custom"
    • PPalkowski's avatar
      PPalkowski
      Helper II
      6 years ago

      Prefect... now I need to break this down and see what you did so I can learn from it

      • Smauro's avatar
        Smauro
        Solution Sage
        6 years ago

        PPalkowski  you could also use mod10 for probably less pqengine effort:
         [Year] - Number.Mod( [Year], 10 )

    • PPalkowski's avatar
      PPalkowski
      Helper II
      6 years ago

      I get an error, I beleive because my year is a number