Power Query Connection to Access Query not returning any values

[Vaslo]

Hi everyone,

I have an Access database that keeps two separate flat tables for forecast and actuals. Sometimes the forecast has different products (or duplicates) so I use a Union Query to provide uniques. I then take that query and cut and paste it into Excel which completes some lookups. This is extra work so I wanted to start using my new Power Query skills.

First, Power Query doesn't even see my Union Query - any reason why this would be the case? This is less important, but curious - I noticed this was also an issue a while back when I tried to connect to the database.
That said, it occurred to me I can skip this Union Step and use Power Query to combine the unique values.

The main issue - when I try to import the data to Power Query, when I try to use Access "Queries" as sources, I am getting a preview that has the correct headings but returns no data and explicitly tells me "The table is empty." Please note the query works fine in Access. Any idea why this would be an issue? Tables from Access work fine, but I'd like to avoid the extra step of converting the query to a table if possible.

Thanks so much!

 

[Matt Allington]

Wow, very strange in both instances. I suggest you click the frown button in Power Query and send feedback to the dev team. I have found them to be very responsive to feedback.

 

[miguel.escobar]

do you think that you could provide a copy of those 2 tables in question? (in an access file) that would help us see what your current scenario is and how to address it. If you could also provide the SQL code for the union that would be great as well.

 

[Vaslo]

do you think that you could provide a copy of those 2 tables in question? (in an access file) that would help us see what your current scenario is and how to address it. If you could also provide the SQL code for the union that would be great as well.

Thanks for replying guys - I would love to just provide but there is so much private info in there that I would need to scrub first. I did take the suggestion to reach out to Microsoft via the "FROWN" to see if they had a response. Like I said, it's clearly working in Access, and refreshes when I run the query, but nothing when I try to open in Power Query. Don't know what I'm doing wrong. I'll let you know when there is a response.

The SQL for the Union Query is:

SELECT [Product Number],[Base Product Number],[Ship To City],[Profit Center Code]
From [Alcoa Query - Act]
UNION SELECT [Product],[Base Product],[Location],[PC]
From [Alcoa Query - FCST];

Thanks as always.

 

[Matt Allington]

Miguel, I built one myself to test it and I confirm the problem.

 

[miguel.escobar]

In that case I'd suggest going the ODBC route.

 

[Vaslo]

I got a response from Microsoft - great suggestion by the way, I never expected they would respond. I need to enable tracing and share the log, so I will do that and let you guys know what they say.