Solved Power Query Automation from Access

[Minty]

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]

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]

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]

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]

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

 

[Minty]

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]

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]

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]

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]

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.