Solved Power Query Automation from Access (1 Viewer)

Minty · Jan 6, 2020

Has anybody done any automation in Excel from Access using Power Query? I have a need to generate a load of transposed data as a final report output, and Power Query can do it easily.

There isn't a great deal on the web about this or I'm having a bad Google day.

The only slightly salient example I can find is here; https://gallery.technet.microsoft.com/VBA-to-automate-Power-956a52d1#content
Which is very Excel orientated? (not that I couldn't adapt it, just was hoping to not re-invent the wheel.)

vba_php · Jan 6, 2020

I've never used PowerQuery, Minty, nor have I been involved much with ETL processes, but do you use Microsoft's ever-so-popular *power BI* dashboard tool? A lot of employers I've interviewed with are obsessed with it and believe it's the end-all solution to their problems. I found this article on Power BI by way of this effort. in addition to this one. Hopefully this response it not totally irrelevant to what you're looking for...

Minty · Jan 6, 2020

No - Power Query comes with Excel and is the tool we are using.

Power BI is a an expensive option by comparison and is actually useless for external reporting, as it requires direct access to the data source in order to work unless you simply want to send an end client a pretty picture.

vba_php · Jan 6, 2020

Minty said:
No - Power Query comes with Excel and is the tool we are using.

I apologize for responding then. I thought it might have been relevant.

isladogs · Jan 6, 2020

Hi Minty
See my thread in the repository: Normalise Spreadsheet Data Easily For Access Using Excel Power Query

Minty · Jan 6, 2020

Hi Colin, thank you, I knew I had seen something by somebody using PQ but couldn't put the name to the face as it were.

That will be a good start.

isladogs · Jan 7, 2020

Hi Minty
You're welcome.
I would recommend you check out this guide to PowerQuery M is for Data Monkey which was brought to my attention by fellow AWF member alansidman

Minty · Jan 7, 2020

Thanks again Colin, I might have a gander at that - I've solved my immediate issue and doing that has shed light on to many other uses potential for PQ.

As you said its inclusion into Access would be a massive benefit, but it looks unlikely to happen based on the current response from here https://access.uservoice.com/forums...suggestions/20083453-add-powerquery-to-access

isladogs · Jan 7, 2020

Yes I agree. I hope you added your vote for it anyway as its a popular request despite the MS response. Just spotted my response from almost 2 years ago. Its number 6 in the list of comments

Minty · Jan 7, 2020

I couldn't remember, but having logged into my account apparently I voted for it in October 2017...

The MS answer provided does seem pretty stonewall, unfortunately.

Solved Power Query Automation from Access (1 Viewer)

Minty

AWF VIP

vba_php

Forum Troll

Minty

AWF VIP

vba_php

Forum Troll

isladogs

MVP / VIP

Minty

AWF VIP

isladogs

MVP / VIP

Minty

AWF VIP

isladogs

MVP / VIP

Minty

AWF VIP

Similar threads

Users who are viewing this thread