Importing specific data from Access to Excel

[brncao]

I have Power Query installed. This is my first time using it.

In the column heading I have a user-defined "Date" (i.e. 4/28/2015) and in the row heading I have "Description" (i.e. "Working Cash"). I would like to run a query in Excel that will get the data from an Access database based on "Date", "Description", "Closing Balance", and then pull that record's "Amount" into that cell. Any idea how to do this? I'd rather not import an entire table into a new worksheet if it can't be helped.

 

[theBardd]

If you create a simple SQL query in your Access database that just selects your target columns, if you then run PQ against that Access database, the query will show in the object list and you can selectit from there.

The M would be something ling

let
    Source = Access.Database(File.Contents("C:\Users\Bob\databases\myDB.mdb")),
    _spListTracking = Source{[Schema="",Item="spTest"]}[Data]
in
    _spListTracking

spTest is the name of my query.

 

[brncao]

I have an idea. I can just connect the data table from a database and hide/lock the tab in excel. Then in a cell, I'll use the DGet function based on a set of criteria. However, I'm having some issues. I keep getting #VALUE!.

=DGET(Working_Cash_Query,"Amount",Criteria!A1:C2)

Here's the criteria cells example

[TABLE="width: 500"]
<tbody>[TR]
[TD]As Of
[/TD]
[TD]Account Number
[/TD]
[TD]Code
[/TD]
[/TR]
[TR]
[TD]1/1/2015
[/TD]
[TD]123
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

I checked the database in Access and the data table query in Excel and they do in fact contain the number in question. I get a #VALUE! error instead. Anyone have any idea why? Office Support says that error comes up when said record doesn't exist, but it does!

 

[brncao]

deleted double post

 

[brncao]

Found what the problem was

Correct:

=DGET(Working_Cash_Query[#All],"Amount",Criteria!A1:C2)

Needed the [#All]

 

[brncao]

How do I make Power Query load a certain date range based on what the user inputs? For example, user inputs 5/8/2015 in cell A1 in Sheet1 and the table in Sheet2 will include only 5/8/2015 and 7 days prior. I don't want the table to load all the days in the year. The workbook gets bigger and bigger over time.

Edit: "Dynamic Parameters" is the word I was looking for. Some tips would be appreciated!

 

[theBardd]

You would import the whole table then filter it based upon that date.

 

[brncao]

You would import the whole table then filter it based upon that date.

I forgot to mention it. I rather not have the user touch the tables. You don't want to have the entire database loaded due to size and performance issues. Filtering should take place before it's imported into a table. I don't want to go into Power Query and filter it manually. I need to delegate this work to others who are not technically-inclined.

 

[theBardd]

The user won't have to filter the data. You would create a PQ model with steps that imports the database, and filters the data. The user would need to refresh it each time, but that is inevitable if the data is variable.

 

[miguel.escobar]

You can create either a function or a parameter table. My friend Ken Puls has an amazing blog post about this specific scenario and I think it might be just what you need:

Building a Parameter Table for Power QueryThe Ken Puls (Excelguru) Blog