Take advantage of Excel Power Pivot and enable write-back to your data warehouse

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.