Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Change date format in query

For a automated reppord i want to change the date in my url to the first day of the previouse month and the last day of the previuouse month. I got it to work with this querry but the url is still not working becasue it is expacting a date format of yyy-mm-dd. How can i change the date format in this querry? I don't want to change the date format of my whole power bi, just in this querry.

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    You may modify your query as below to see if it helps.

    let 
    start = Date.ToText( Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow())，-1 )),"yyyy-mm-dd" ),
    end = Date.ToText( Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow())，-1 )),"yyyy-mm-dd"),
    ...
in
    ...

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello Anonymous 

     

    here some example how you can format a date to yy-mm-dd

    = Date.ToText(Date.From(DateTime.FixedLocalNow()),"yy-MM-dd")

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    You may modify your query as below to see if it helps.

    let 
    start = Date.ToText( Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow())，-1 )),"yyyy-mm-dd" ),
    end = Date.ToText( Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow())，-1 )),"yyyy-mm-dd"),
    ...
in
    ...

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello, thanks for helping me with the date time issue, it worekd as intended when changing mm to MM. However, i now get the following error in the querry:

     

    This has probably something to do with the date variables but i have no idea why it is not working

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      5 years ago

      Hi, Anonymous 

       

      Based on the error message, it means Web.Contents only need 1 or 2 parameters while it provides 6 arguments. You need to use text connection symbols to connect text to form a url instead of comma separated.

       

      Best Regards

      Allan

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.