Trying to get decade from year

[PPalkowski]

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?

[solexecution · Accepted solution]

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]

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

[Smauro]

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

[camargos88]

Hi @PPalkowski .

Try this one:

Text.Start(Date.ToText([Column1], "yyyy"), 3) & "0s"

[PPalkowski]

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