Power Query connected to sql database Advanced Editor filter is reading from the whole DB bucket

[Mauri_Castro_70]

Hi All,

I really need your help!

I am trying to insert a code in the Advanced Editor in power query so that I get the data from the [table] only from the current Fiscal Year (i.e 2024 = April 01, 2023 thru March 31, 2024). This code should look at another column named [ActualDate] in order to define if the record (row) belongs to the current FY or not. This would be step one.

Now, I am connected to one my companies sql databases and when I tried to enter a code I found on another forum, power query will all always try to read from all the tables in sql database bucket/source: "Sql.Database("mydatabase,2050", "MCAA"  and not just from the table I am connected to: Source{[Schema="rev",Item="Daily_Snapshot"]}[Data]. This the current Advanced Editor code:

let
Source = Sql.Database("mydatabase,2050", "MCAA"),
rev_Daily_Snapshot = Source{[Schema="rev",Item="Daily_Snapshot"]}[Data],

// Added this step to get the current Fiscal Year
CurrentYear = Date.Year(DateTime.LocalNow()) + Number.From(Date.Month(DateTime.LocalNow()) >= 4),

// Filter the data based on the current Fiscal Year
FilteredTable = Table.SelectRows(Source, each Date.Year([ActualDate]) = CurrentYear)
in
FilteredTable

So, how do I fix the issue, I am lost; please help!

Thanks in advance for the help.

Mauricio

[Fowmy · Accepted solution]

@Mauri_Castro_70 

In the FilteredTable step, you are using Source instead of rev_Daily_Snapshot which is the table you want to filter.

Try this code:

let
Source = Sql.Database("mydatabase,2050", "MCAA"),
rev_Daily_Snapshot = Source{[Schema="rev",Item="Daily_Snapshot"]}[Data],

// Added this step to get the current Fiscal Year
CurrentYear = Date.Year(DateTime.LocalNow()) + Number.From(Date.Month(DateTime.LocalNow()) >= 4),

// Filter the data based on the current Fiscal Year
FilteredTable = Table.SelectRows(rev_Daily_Snapshot, each Date.Year([ActualDate]) = CurrentYear)
in
FilteredTable

[Mauri_Castro_70]

Hi Fowmy,

Thanks so much for the prompt reply.

You were 100% right... by making the change you suggested, now the code worked just fine. However; I did not get the filtered data I was expecting. You see, I need a M code that will filter the data/table so that I ONLY get data from the current Fiscal Year. My Company's fiscal year goes from April 1st - March 31st; the code above is filtering the data only for the current year, in this case January 2024. Whereas I should be getting data from April 1st, 2023 up to the current date (now) since we are still in the period for FY 2024. Can you help me with that?

Thanks again for the help.

Regards!