Forum Discussion
function to convert date to integer
hi,
i need to create a function in power query which takes any date as input and converts that to int. For example 01/01/2001 (mm/dd/yyyy) equals to 1 as integer. . Taking this as reference i need to convert any dates to integer.
so..it should be like below....basically for each successive date int will be incremented by 1. Please suggest
2 | 1/2/2001 |
3 | 1/3/2001 |
4 | 1/4/2001 |
5 | 1/5/2001 |
6 | 1/6/2001 |
7 | 1/7/2001 |
8 | 1/8/2001 |
9 | 1/9/2001 |
10 | 1/10/2001 |
11 | 1/11/2001 |
12 | 1/12/2001 |
Hi, srk_powerbi
Please try the following steps:
- Create a new table by copying the date column
- Use the standard ribbon earlier function to get the minimum date
- Merge the two tables according to the date column and fill the minimum date down
- Subtract the minimum date from the date to get a new column
- Get the number of days with Duration fuction, and then add one to the number of days
Step 4 and step 5 I combined it into the following formula
= Table.AddColumn(#"Filled Down", "New column", each Duration.Days([Date]-[Min.Column1])+1)I created a simple sample to illustrate this.
Sample:
Result:
Code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdCxDYNAAEPRXagj3dmGJDcLYv81EA3yL3/lJ5/n5qHhaW3X5wl3pGPvODq+Hb+Of8fq0ETBICAEhcAQHAJEkAgUwWJYzD9gMSyGxbAYFsPisqTvT9+fvj99f/r+9P3p+zOwszA6UTAICEEhMN77rxs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, Min, {"Column1"}, "Min", JoinKind.LeftOuter), #"Expanded Min" = Table.ExpandTableColumn(#"Merged Queries", "Min", {"Column1"}, {"Min.Column1"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Min",{{"Min.Column1", type date}}), #"Filled Down" = Table.FillDown(#"Changed Type1",{"Min.Column1"}), #"Added Custom" = Table.AddColumn(#"Filled Down", "New column", each Duration.Days([Date]-[Min.Column1])+1), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min.Column1"}) in #"Removed Columns"Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
one way is
new column =
year([date])*10000 + month([date]) *100 +day([Date])
new column=
or rankx(table, [date],,asc,dense)
Can't you just add an index column?
It is 'increment for each date' or '# of days from lowest date'?
So... (DD/MM/YYYY's below - US Format)
1/1/2001 = 1
1/3/2001 = 2
5/1/2001 = 3
Or
1/10/2001 = 1
1/15/2001 = 5
2/1/2001 = 22
Hi, srk_powerbi
Please try the following steps:
- Create a new table by copying the date column
- Use the standard ribbon earlier function to get the minimum date
- Merge the two tables according to the date column and fill the minimum date down
- Subtract the minimum date from the date to get a new column
- Get the number of days with Duration fuction, and then add one to the number of days
Step 4 and step 5 I combined it into the following formula
= Table.AddColumn(#"Filled Down", "New column", each Duration.Days([Date]-[Min.Column1])+1)I created a simple sample to illustrate this.
Sample:
Result:
Code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdCxDYNAAEPRXagj3dmGJDcLYv81EA3yL3/lJ5/n5qHhaW3X5wl3pGPvODq+Hb+Of8fq0ETBICAEhcAQHAJEkAgUwWJYzD9gMSyGxbAYFsPisqTvT9+fvj99f/r+9P3p+zOwszA6UTAICEEhMN77rxs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, Min, {"Column1"}, "Min", JoinKind.LeftOuter), #"Expanded Min" = Table.ExpandTableColumn(#"Merged Queries", "Min", {"Column1"}, {"Min.Column1"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Min",{{"Min.Column1", type date}}), #"Filled Down" = Table.FillDown(#"Changed Type1",{"Min.Column1"}), #"Added Custom" = Table.AddColumn(#"Filled Down", "New column", each Duration.Days([Date]-[Min.Column1])+1), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min.Column1"}) in #"Removed Columns"Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.v-angzheng-msft thanks for solution. this works. I have another question, when i set up incremental refresh on the table , i choose to store last 3 years of data, however after refresh completes i see the last date in the table is 01/01/2019, Does it only consider 'year' because it supposed to store from 05/01/2018 since i mentioned last 3 years from 05/01/2021? Please suggest
Hi, srk_powerbi 😀
I'm very glad that my method is useful to you, but your other question is not related to this topic. Opening a new topic will be a better way. In this way, your question will be more targeted, and it will also provide reference for other people who encounter similar problems. If it is convenient for you, please open a new topic and provide a detailed description, It would be better to have relevant sample data or screenshots, so that your problems will be solved faster.
Best Regards,
Community Support Team _ Zeon Zheng
