Power Query is Awesome, but…..

With Microsoft’s focus on Business Intelligence in Excel and with Power BI Designer(www.powerbi.com), the most powerful tool in the toolshed that sets PowerBI aside from competition is Power Query. It can read data from virtually anywhere. From structured data sources like SQL Server, SSAS, Oracle, MySQL, Access, Oracle and DB2 to JSON/Odata, Facebook, SalesForce, CSV, Excel and Active Directory. It can also read whole folders of files, like a hive. Power Query is more than a query tool, it’s a fully fledged ELT(popular version of ETL) tool. You can Extract, Load then Transform to Power Pivot, and even to a SQL Server with an Excel Add-in. This is extremely powerful in enabling self service BI. However, there is no way to schedule Power Query in an efficient way; you currently have to manually kick off the jobs. But Wait – There is a solution!!! Power Update from the PowerPivotPro team enables you to schedule Power Query Excel Workbooks. Download a free version of this popular tool. This blog article from Rob Collie explains how it works. Check it out.

Excel – Power Query and Power Update

Power Query – Your next ETL solution – With Power Update

When Power Query was launched as part of Power BI in February of ’14, the BI community applauded the flexibility and power that Power Query represented. With the current version of Power Query, data enthusiast can collect and refresh information from virtually any data source into Power Pivot.

As the product evolves, we get to see more sophisticated users and they are polarizing into:

• Data User
• The Intermediate
• The King of DAX

The intended use of Power Query was to pull data from virtually any data source ranging from Active Directory to Salesforce and Facebook to Power Pivot as a self-service tool. This is fantastic self-service functionality for most users.

Additionally, the “Intermediate” and “King of DAX” users see the value of loading Facebook, Salesforce, ERP, Active Directory, oData, Web Tables not only to a Power Pivot on their desktop, but all the way to the SQL Server Enterprise Data Warehouse. More specifically; the ODS.(Operational Data Store)

With Power Update from Power Planner, Power Query becomes a complete ETL solution where you really can do Self-Service ETL and not only Self Service BI. The best example is loading Salesforce data into PowerPivot and then further loading this data into the Enterprise DW, without a separate ETL process in SSIS. All data is loaded straight from Power Query, and brings another dimension into Self Service BI. All Power Query and Power Pivot jobs can be scheduled to run around the clock.

 

Contact us today for a demo version.