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 pow...
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

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity 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