Category Archives

44 Articles

Top 3 of what is new in Power Query for Excel

Just recently the December update of Power Query for Excel has been published. See this blog post on the Power BI for the details: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx.

In this post I want to give you my personal top 3:

1. Automatic detection of table relationships. Like PowerPivot if you import multiple tables from the same source Power Query now detects the relationships and brings them over to the Power Pivot Data Model automatically.

2. Connectivity to new data sources. Now supported: Exchange, Dynamics CRM Online, JSON light and Sybase IQ.

3. Fill Down transformation. Fills all empty cells in a column with the value of the first non-empty cell above them. Very handy for those matrix like Excel sheets!

I will come back to some of these in later posts. Happy Power Querying!

The wait is almost over…

It’s almost Christmas!

Well, uh, that’s not actually why I wrote this post. I noticed a note on the Power BI blog that says: “In a few days we will enable the ability to explore your own data with Q&A. You can add your own models to Q&A and begin to explore them with natural language query.” (See full post here: http://blogs.msdn.com/b/powerbi/archive/2013/12/13/one-step-closer-to-simplifying-data-analysis-and-visualization-new-features-added-to-power-bi-for-office-365-and-power-query-add-in-for-excel-preview.aspx)

Read that again! This means that you can upload your own Power BI workbooks and use Q&A to have a conversation with your data. How cool is that! Minority report anyone?

Power BI Demo Contest

I want to draw your attention to a contest that we run: the Power BI Demo Contest. All you need to do is create a short video of something cool you built using Power BI and share it. You could win great prizes!

More info is on Facebook: https://www.facebook.com/microsoftbi/app_112813808737465

Good luck!

Working with Azure and HDInsight from SSIS

A while ago a whitepaper was published on how to work with Azure and HDInsight (Hadoop on Azure) from SSIS. In that whitepaper some code samples were given. That code is also available here (including some components): http://code.msdn.microsoft.com/SSIS-Packages-Sample-for-2ffd9c32 . After you have downloaded the zip make sure you have Visual Studio installed, start the Developer Command for Visual Studio as Administrator and run the ‘deploy_SSIS_packages_and_components.bat’ file which is included in the zip. This will install some of the DLLs included into the GAC. Then, you can open the solution in Visual Studio.

The solution includes the following sample SSIS packages:

  • PigSqoopPackage: shows how to work with Pig and SQOOP tasks
  • HadoopJobAutomation: shows how to start jobs on Hadoop and how to consume results
  • ComplexSourceDestination: shows how to get data from Azure Blog Storage and save the results into various targets, including Azure Blob Storage.
  • BlobSourceTestPackage: sample package showing how to read data from Azure Blob Storage.
  • BlobDestinationTestPackage: sample package showing how to write data to Azure Blob Storage.

The first two packages essentially contain some script tasks with complete samples on how to work with Piq, SQOOP and Hadoop jobs respectively. The other packages use the components provided and provide a quick start on getting data from Azure Blob Storage and getting data into Azure Blog Storage using SSIS.

%d bloggers like this: