Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
5 years ago
Solved

Index ID from Date Column using Power Query

Hi,

 

I have a Date Column and would like to create INDEX column based on Date values.

 

For example;

 

I have Date Column as show in below figure and from Date Column I want to create ID (Index Column) column using Power Query.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NOTE: I have dates info for almost 40 Years. (1994-2034)

Any suggestion/Help Appreciate!

 

Thanks

Need Help

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi damit23183 

    Try this.  It's loading a column of dates from a table called Dates

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ID", each Text.From(Date.Year([Date])) & Text.PadStart(Text.From(Date.Month([Date])),2,"0") & Text.PadStart(Text.From(Date.Day([Date])),2,"0"))
in
    #"Added Custom"

     

     

    I'm using d/m/y/ not sure what you have.  You may need to swap around the Date.Month and Date.Day parts of the query.

    Phil 

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

    • damit23183's avatar
      damit23183
      Microsoft Employee
      5 years ago

      Thank you Philip for your inputs. 

       

      Rellay appreciate it!