Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic M Code

I have zero knowledge on M code. I am getting a Date table from the SQL database to power bi cube. It's dates goes up to year 2024. I want to limit it to until next March 31from the current date dyna...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    Anonymous I think I got it!

     

    let
    Source = Sql.Databases("DatabaseName"),
    AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data],
    dbo_DimDate = AdventureWorksDW2012{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, 
        each if [FullDateAlternateKey] <= #date(Date.Year(DateTime.LocalNow()), 3, 31) 
        then [FullDateAlternateKey] <= #date(Date.Year(DateTime.LocalNow()), 3, 31) 
        else [FullDateAlternateKey] <= #date(Date.Year(DateTime.LocalNow()) + 1, 3, 31)
    )
in
    #"Filtered Rows"

     

     

    Try it out and let me know!  ğŸ™‚

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Anonymous 

If you just need a list of dates from now until the next Mar 31 then you can create this list in Power Query with this M code.

 

let
    Source = 
    
            if DateTime.Date(DateTime.LocalNow()) >= #date(Date.Year(DateTime.LocalNow()),3,31)
            
            then List.Generate(() => DateTime.Date(DateTime.LocalNow()) , each _ <= #date(Date.Year(DateTime.LocalNow())+1, 3, 31), each Date.AddDays( _ , 1))

            else List.Generate(() => DateTime.Date(DateTime.LocalNow()) , each _ <= #date(Date.Year(DateTime.LocalNow()), 3, 31), each Date.AddDays( _ , 1))
in
    Source

 

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

PhilipTreacy 

I already have the Date table which is generated by the database up to 2024 I want to limit it to next march 31 from the current date dynamically. If the current date is today it should be ended on 3/31/2021 and if the current date  4/1/2021 end date should be 3/31/2022.

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    Hi Anonymous.

     

    I worked this out using AdventureWorks...hopefully this helps you ğŸ™‚

    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey], if [FullDateAlternateKey] < #date(Date.Year(Date.From(DateTime.LocalNow())), 3, 31) then [FullDateAlternateKey] < #date(Date.Year(Date.From(DateTime.LocalNow())), 3, 31) else [FullDateAlternateKey] < #date(Date.Year(Date.From(DateTime.LocalNow())) + 1, 3, 31))
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      littlemojopuppy 

      I used the code as follows

       

      = Table.SelectRows(dbo_DimDate , each [Date], if [Date] < #date(Date.Year(Date.From(DateTime.LocalNow())), 3, 31) then [Date] < #date(Date.Year(Date.From(DateTime.LocalNow())), 3, 31) else [Date] < #date(Date.Year(Date.From(DateTime.LocalNow())) + 1, 3, 31))

      But I'm getting the following error

       

      Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        5 years ago

        Can you post a screen shot of all the transformation steps in the Advanced Editor from Power Query along with a screen shot of the error?