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 (Inde...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    damit23183 , Create a new column like

    Date.ToText([Date], "yyyyMMdd")

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!