Forum Discussion
Using Power BI Power query with a microsoft access database tabel
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?
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.)
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
6 Replies
- SamWiseOwl
Super User
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?
- 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.)
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
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
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
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
