UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Powerquery In Excel, Office 2010    
 
   
MadPiet
post Apr 10 2020, 06:30 PM
Post#1



Posts: 3,796
Joined: 27-February 09



Sorry for the lame title...

In another post on here somewhere, someone was trying to import and transpose/transform basically an entire folder of Excel files. It's stupid easy in PowerQuery, but I haven't had 2010 installed anywhere since I can't remember when. I think PowerQuery (and the whole DAX etc part of Excel) is only available as an add-in for that version of Excel... Is the Folder source available on that version? In Excel 2016, you go Data > New Query > From Folder … and you can basically query all the files in the folder at once. Is that available in Excel 2010?

Thanks!
Go to the top of the page
 
Doug Steele
post Apr 11 2020, 11:49 AM
Post#2


UtterAccess VIP
Posts: 22,305
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Microsoft Power Query for Excel says
QUOTE
The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. You can still download this version, but if you want to access new Power Query capabilities please upgrade to Excel 2016 or newer, where Power Query capabilities are integrated within the Data tab.


--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
MadPiet
post Apr 11 2020, 11:57 AM
Post#3



Posts: 3,796
Joined: 27-February 09



Doug,

Spot on. Thanks a mil! Exactly the part I needed - the folder source!

Pieter
Go to the top of the page
 
MadPiet
post May 1 2020, 01:14 PM
Post#4



Posts: 3,796
Joined: 27-February 09



Okay, got everything working using several PowerQueries and then one to merge them all together.

Now, is there an easy way in Excel 2010 to force the refresh of the data? In Access, I could create an AutoExec macro and then open the file on a schedule using something like Windows Scheduler. (I wonder if I could do that with PowerShell???) Any ideas how to do that part?

Thanks!
Go to the top of the page
 
isladogs
post May 1 2020, 02:20 PM
Post#5


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


For anyone interested, I recommend this excellent book for learning about PowerQuery/Get & Transform: M is for (Data) Monkey

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
MadPiet
post May 1 2020, 02:56 PM
Post#6



Posts: 3,796
Joined: 27-February 09



I have that book. I have Gil Raviv's book too. Data Monkey is a great intro... What I like about Raviv's book is that he covers a lot of patterns and walks you through dealing with them. Super handy!

And then there's Reza Rad's stuff. (I went to one of his full day presentations on PowerQuery… absolutely worth it!) www.radacad.com

Rob Collie wrote an add-in or something that automates the refresh, but it's $$$. That's why I was hoping I could call the refresh in something like an Excel version of an AutoExec macro.
Go to the top of the page
 
isladogs
post May 1 2020, 03:32 PM
Post#7


UtterAccess VIP
Posts: 2,404
Joined: 4-June 18
From: Somerset, UK


Sounds like you're way ahead of me in that case 😎

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 04:36 AM