Did you know that if you learn to use Power Query to “Get” and transform data in one of these Microsoft Business Intelligence & data analysis tools, you actually have the skills to use any of them? Power Query is an amazing technology for acquiring and wrangling data from a vast portfolio of data sources, and it can be used to perform simple and very advanced data transformations. In my interview with Chris Webb at the 2017 PASS Summit, he said that Power Query is everywhere and being added to more and more Microsoft products. We can expect to see Power Query in web-based tools for use with cloud services. Today, it is the “Get Data experience” on the Data ribbon in Excel, Power BI Desktop and now in SQL Server Data Tools (SSDT) for SSAS 2017 Tabular projects. The adoption rate for Power Query has been fantastic and it is a tool that once you learn the basics, you just can’t go back and use older tools. Power Query makes data wrangling and transformations a creative process rather than simply an exercise in connecting the dots (or tasks and transforms). The Query Editor generates “M” Script, a powerful and flexible data mashup language that translates transformation steps into native query code.
In this tutorial, I use Excel 2016, Power BI Desktop and SSDT for Visual Studio 2017 to create three different sample projects. Using a few of the lab data files from my Mastering Power BI Workshop course, I import a folder full of CSV files; using the same technique in each of the three tools. This demonstration makes the point the Power Query generally works the same in workbook, desktop and enterprise BI and analytics solutions. There are a few subtle differences to be aware of. By default, Excel uses the Power Query output to populate worksheet tables in addition to the embedded Power Query data model. In the new SSAS Tabular designer within SSDT, you define connections first, and then use the Tabular Model Explorer to import tables from established connections. File, folder and data source connections are also managed a little differently in an SSAS project because the deployed databases is managed by the SSAS service account.
Great information shared. if you would like to know more about the new features in power BI you can visit this article http://bit.ly/2AMdjyq which I found on the net