Using Power BI Power query with a microsoft access database tabel

[roryv2002]

Hi,

i am using Power bi with a table in a access database and i am struggling to amend the M code to only pull through 1 years worth of data , when i use PBI with databricks tables, i write SQL to do this but i am struggleing to do this with access

in power query my navgiation code is showing as 

= Access.Database(File.Contents("J:\example\example\Strategy\CL_CMI\Automated\Databases\example.accdb"), [CreateNavigationProperties=true])

i want to add in select* where loss data >= 01/01/2024 but i get a error what ever i do. and when i click the spanner to get the box up, it doesnt show like when using databricks tables, am i doing this the wrong way or missing something?

[v-vpabbu]

Hi @roryv2002,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @roryv2002,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @roryv2002,

Thank you for reaching out to Microsoft Fabric Community Forum.

When working with Power Query and a database table, you cannot apply SQL-like conditions directly. However, you can filter data after it is loaded into Power Query.

Import the table or query into Power Query without attempting to filter it at the source. Use Power Query’s interface to filter rows:

Select the column you want to filter by.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"

Regards,

Vinay Pabbu

[ToddChitt]

Is your Power Query returning data from the Access table? If so, is the column you want formatted as a Date type? If so, can you not just put in a Filter Rows step in Power Query? 

(FYI, your SQL statement of "select * where loss data >= 01/01/2024" is wrong on a few levels, like missing a FROM clause just for starters.)

[SamWiseOwl]

Hi @roryv2002 

I don't think that connector is delegatable, I'm sure someone will let us know.

In the mean time have you considered using the ODBC connector?

https://www.youtube.com/watch?v=W-KItvBkwDg