Dynamic M Code

[Anonymous]

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 dynamically.

Thank you 

[littlemojopuppy]

@Anonymous sorry this and the other question took a couple of days to resolve.  Would appreciate you marking the solution in both this and the original question.

Happy holiday!

[PhilipTreacy]

Hi @Anonymous 

Yes I understand that you have a date table from SQL but if you only want a finite list of dates from today until the next Mar 31, isn't it just as easy (easier?) to create that list rather than load a list of dates then manipulate them to exclude dates/rows you don't want?

Or maybe there is more to it?  Are you loading more than just dates from SQL?  If so, are you talking about needing to filter out rows so that you keep any additiona data in each record, not just the dates?

regards

Phil

[Anonymous]

@PhilipTreacy 

Date table is starting from 2015 and it already has fields that required for calculations, and past dates are needed for Time Intelligence-based calculation. Further financial year starts from April 1. With the dates goes up to 2024 mess up the Time Intelligence calculation, that's why I need to limit the date table on FY end date with it's other information. 

[PhilipTreacy]

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]

@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]

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]

@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]

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?

[Anonymous]

@littlemojopuppy 

this is the database

[Anonymous]

@littlemojopuppy 

[littlemojopuppy]

Can you post a view of all transformation steps from the Advanced Editor?  (There's a button in the Ribbon in Power Query).  If you have things like connection strings, IP addresses, or anything sensitive you probably want to obscure those first.

[Anonymous]

@littlemojopuppy 

[littlemojopuppy]

@Anonymous  apologize for the delay...had to grocery shop with the wife.

Stuff like this is why I don't like M code.  🙄  Give me a couple minutes to play with it!

[Anonymous]

@littlemojopuppy 

OK

[littlemojopuppy · Accepted solution]

@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!  🙂

[Anonymous]

Dear @littlemojopuppy 

It worked, thank you for your help. Much appreciated your effort.

[littlemojopuppy]

So I turned you on to this idea in your other post.  M code is not my strongest suit either.

What if you try something like Date <= #Date(Date.Year(Date.From(DateTime>LocalNow())) + 1, 3, 31) so you don't have to maintain it?  It's probably not syntactically perfect but the idea should work.