Forum Discussion

smeg's avatar
smeg
Helper I
2 years ago
Solved

Hekp with M Query

Hi,      I am trying to retrieve  data into one of the query in power query editor  using M for  last 18 months,but below query retrieves 2 years of data. Can you please let me know the formula to ...
Need Help
  • smeg's avatar
    smeg
    2 years ago

    Below query  worked for me as per my requirement,to retrieve  data for  last 18 months,

    As I also  need a number format not a date format.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi smeg - if you enter the following function the result is 20211231:

= (((Date.Year(Date.AddDays(DateTime.LocalNow(), -1 )) - 2) * 10000) + 1231)

// 20221231

 This result is number, not a date.  

 

Could you use the following function instead.

= Date.AddMonths( Date.StartOfMonth( DateTime.LocalNow() ) , -18)

// 01/06/2022 00:00:00

Another thing to consider is the Data Type of the [PE_Date_Int] column.  Is it Date?  The example "06012022" suggest that it might be Text.

smeg's avatar
smeg
Helper I
2 years ago

Hi 

Anonymous 

Data Type of the [PE_Date_Int] column is integer(number)  not a date,How can i cast/convert below function accordingly because when i use below one the data refresh is taking forever,because of comparison between number and date.

= Date.AddMonths( Date.StartOfMonth( DateTime.LocalNow() ) , -18)

 

Thanks..

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago
    = Number.From(Date.ToText(Date.AddMonths( Date.StartOfMonth( DateTime.LocalNow() ) , -18),"yyyyMMdd"))
    • smeg's avatar
      smeg
      Helper I
      2 years ago

      Below query  worked for me as per my requirement,to retrieve  data for  last 18 months,

      As I also  need a number format not a date format.