Published on July 20th, 2015 | by Fredrik Knalstad1
Power Query Q&A
For those of you that have not heard about Power Query and wants to start with it. You have come to the right blog post.
Power query has taken me by storm and thought I could share some of the information with you.
So let’s start with some Q&A on Power Query.
What is power query?
Power Query is an Excel add-in and is part of the Microsoft Power BI self-service solution. With Power Query you can connect to different data sources and extract data from that source. With the Power Query Editor you can transform that data and do calculations that fits your needs. Power Query is updated on monthly bases so new features will be included monthly.
Power Query is part of the 4 P’s in Excel
Power Query – Discover, import and reshape data
Power View – Data Analytics with in memory technology.
Power View – Interactive Visualization of data
Power Map – Geographical data visualization though 3D mapping. (Bing)
What versions do Power Query come in?
Power Query comes in 32bit and 64bit version, it all depends on the version of Excel you have installed.
32bit Excel installed = 32bit Power Query
64bit Excel Installed = 64bit Power Query
The 64bit version is recommended if you are working with large amount of data. The 32bit version will only use up to 1 gb of memory, but the 64-bit version of Power Query can use as much virtual memory that’s available on the system.
What is the Excel requirements?
Excel 2010, Excel 2013, Excel 2016 (preview)
*Power query is available for Excel 2010 but the Load to Datamodel is not available in that version.
Power view and Power Map is not available for Office 2010. Recommend to upgrade to Exel 2013 or Excel 2016 for full functionality.
*Power Query is default integrated into the Excel 2016.
Is Power Query free?
Yes, Power Query is free and available as long as you have a licenced version of Office Excel.
How do I add Power Query to Excel?
This step only applies to Excel 2010 and Excel 2013. Power Query is integrated into Excel 2013.
What is the Power Query Formula Language?
In the Power Query you can do most of your transformation and calculations through the graphical interface. But if you want to add you custom touch or need functionality that is not available in the GUI. You most use the Power Query Formula Language (informally known as M). To get more into the bits and pieces on the language -> Power Query Formula Language.
How is the best method to connect to SQL data?
You can connect to SQL in two ways. Either create a native query to get the spesific data you want out of our database or use the navigator to browse though the Data Catalog.
What is query Folding?
Query folding is the technique of sending the query back to the source system to offload Power query and to optimize the query performance.
This way the transformation can take place at the source system before it is sent to Power Query. When something cant be folded , Power Query does the work locally. Column filters, row filters, joins, group by, pivot, unpivot, numeric calculations, aggregations gets folded.
Some of the supported sources are: Relational sources (SQL, Oracle), OData, Exchange, Active Directory. Overview of sources that support Query folding. -> LINK more detailed information check out -> BLOG