Forum Discussion
Refresh M Query - Correct Syntax For Dates
I am exporting all data from a log table in App Insights to Power BI as an M query. I want to create a semantic model or dataflow in Power BI that allows me to refresh the data to retrieve the latest values from all columns in the table. I have been able to export the data and add it as a blank query in the advanced editor, but I am struggling with the correct way to use the timestamp field in the table to write in the date range in the M query.
Essentially, I'm hoping to (upon retrieval of new data) to have the query pull data from the last 90 days of data up to the current date/time. I am currently just getting the date range I set when querying the log data in App Insights before exporting the query. I only need the events and timestamps in the table to create some basic funnels, so I think defining the date correctly to get new data is the only hurdle.
For example, in SQL I would write it as:
timestamp>=DATEADD(DAY,-90,CONVERT(date,GETDATE()))
- Anonymous2 years ago
Hi Anonymous ,
Please try this:let Source = YourTableName, // Replace with your actual data source CurrentDate = DateTime.From(DateTime.Date((DateTime.LocalNow()))), StartDate = DateTime.From((Date.AddDays(CurrentDate, -90))), FilteredRows = Table.SelectRows(Source, each [datetime] >= StartDate and [datetime] < Date.AddDays(CurrentDate,1)) in FilteredRowsBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group
1 Reply
- AnonymousNot applicable
Hi Anonymous ,
Please try this:let Source = YourTableName, // Replace with your actual data source CurrentDate = DateTime.From(DateTime.Date((DateTime.LocalNow()))), StartDate = DateTime.From((Date.AddDays(CurrentDate, -90))), FilteredRows = Table.SelectRows(Source, each [datetime] >= StartDate and [datetime] < Date.AddDays(CurrentDate,1)) in FilteredRowsBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum -- China Power BI User Group