Forum Discussion

shamilka's avatar
shamilka
Frequent Visitor
2 years ago
Solved

Converting a number to the current date

I want to do following two things in power query

 

1. My database contains dates as a number as per the table below. I want to convert it to a date recognized by Power BI.

2. Need to develop a power query code to get the data recorded for the past 3 days from today. 

 

Note: the Date number is eqal to the number of days from a specific constant date (Ex: Date number = number of days from 13.12.2000 = 1550)

 

Part noBatch noPurchase OrderDate number
155100008A10031550
153100008A10041549
154100008A10051543
155100008A10061540

 

Thank you

Need Help
Show and Tell
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi shamilka, last step is blank table because sample data are year 2005.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1VdJRMjQAAgsgwxHIMgYJmJoaKMXqgOSN0eVNwPImllB5E3R5U4i8MVQew3wziDzQ/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part no" = _t, #"Batch no" = _t, #"Purchase Order" = _t, #"Date number" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Part no", Int64.Type}, {"Batch no", Int64.Type}, {"Purchase Order", type text}, {"Date number", Int64.Type}}),
    Ad_Date = Table.AddColumn(ChangedType, "Date", each Date.AddDays(#date(2000,12,13), [Date number]), type date),
    FilteredLastThreeDaysFromToday = Table.SelectRows(Ad_Date, each [Date] >= Date.AddDays(Date.From(DateTime.LocalNow()), -2))
in
    FilteredLastThreeDaysFromToday

     

1 Reply

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi shamilka, last step is blank table because sample data are year 2005.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1VdJRMjQAAgsgwxHIMgYJmJoaKMXqgOSN0eVNwPImllB5E3R5U4i8MVQew3wziDzQ/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part no" = _t, #"Batch no" = _t, #"Purchase Order" = _t, #"Date number" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Part no", Int64.Type}, {"Batch no", Int64.Type}, {"Purchase Order", type text}, {"Date number", Int64.Type}}),
    Ad_Date = Table.AddColumn(ChangedType, "Date", each Date.AddDays(#date(2000,12,13), [Date number]), type date),
    FilteredLastThreeDaysFromToday = Table.SelectRows(Ad_Date, each [Date] >= Date.AddDays(Date.From(DateTime.LocalNow()), -2))
in
    FilteredLastThreeDaysFromToday