Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 no | Batch no | Purchase Order | Date number |
155 | 100008 | A1003 | 1550 |
153 | 100008 | A1004 | 1549 |
154 | 100008 | A1005 | 1543 |
155 | 100008 | A1006 | 1540 |
Thank you
Solved! Go to Solution.
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
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