Data Explorer

Unpivoting Data in Data Explorer

Unpivoting Data in Data Explorer

March 13, 2013

Data Explorer is generating a lot of positive buzz within the community and everyone I speak to seems to be really excited about this. (If you are wondering what Data Explorer is, you might want to quickly catch up on my previous post – Introduction to Data Explorer Preview for Excel). And just today, the Data Explorer team announced A new build of “Data Explorer”, and an Auto Update feature (must say I really like the idea of Update button). This was followed by a post from Jamie Thomson aka SSIS Junkie (blog | twitter) on the query language ( M ) in Data Explorer and you can read more about that here. All this made me really excited and I also decided to contribute something through this post. Unpivot rows in data explorer It is a pretty common requirement to unpivot data, especially when you are scourging the net for open data. Data Explorer currently does not have any feature to unpivot data but the query language looked pretty solid and I decided to give it a try (actually, this exercise started as a way to test the new query editing functionality in Data Explorer). Well, I did succeed though it might not look that easy (if you do find an easier way, please share it!). Read on for the solution:- 1) The source for my post is given below source data I just made a simple table in the excel spreadsheet which gives the Sales by State for the years 2010 to 2012. I then click on the From Table option in Data Explorer tab to get the query window as shown below. Import data from excel 2) I add a new column called JCol with a value of 1 using the expression below

= Table.AddColumn(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content], “JCol”, each 1)

Add join column to first table  Now this is my first table, and I store the expression with me. 3) My next objective is to make a transpose of the original table. This can be done with the expression below

= Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content])))

Transpose table 4) Now we need to rename the State Column to Year.

= Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”})

Rename column 5) Now, we need to add a column to this resultant table called JCol (just as we did to the first table in Step 2).

= Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”}), “JCol”, each 1)

Add join column to second table 6) You might be wondering at this stage why we created the JCol column in both the tables. The reason is that we need to cross join both of these tables so that we get extra rows, and the join column is going to be JCol. Since the values for the join column are all the same, we get a cross join. To do the join of both the tables, delete the existing expression and enter the expression below

= Table.Join(Table.AddColumn(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content], “JCol”, each 1), “JCol”, Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”}), “JCol”, each 1), “JCol”)

Join both the table expressions 7) Now add a new calculated column which will give you the value in NC column if State is NC, SC column if State is SC and FL column if State is FL. Add custom column for Sales The expression for the calculated column is given below

if [State] = “NC” then [NC] else if [State]= “SC” then [SC] else [FL]

custom column expression Now your result should look like shown below end result of custom calculation 8) Now all you have to do is to rename the Custom column to Sales and hide the unnecessary columns. Then you will be able to see the unpivoted data as shown below rename and hide unnecessary columns 9) Also, have a look at how the actual query looks like in the advanced query editor Advanced query editor I would like to see a word-wrap option for the advanced query editor so that I don’t have to scroll over to the right to see long formulas. Apart from that, looks great! Don’t forget to share your comments as well as your posts / experiences with Data Explorer and it’s query language. Update I got quite some requests asking for the actual query. So here it is

    Source = Table.Join(Table.AddColumn(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content], “JCol”, each 1), “JCol”, Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name=”Table37″]}[Content]))), {“State”,”Year”}), “JCol”, each 1), “JCol”),
   InsertedCustom = Table.AddColumn(Source, “Custom”, each if [State] = “NC” then [NC] else if [State]= “SC” then [SC] else [FL]),
    RenamedColumns = Table.RenameColumns(InsertedCustom,{{“Custom”, “Sales”}}),
    HiddenColumns = Table.RemoveColumns(RenamedColumns,{“2010”, “2011”, “2012”, “JCol”, “NC”, “SC”, “FL”})

Remember to change the name of the table name when you use it.

Posted by SQLJason, 5 comments
Introduction to Data Explorer Preview for Excel

Introduction to Data Explorer Preview for Excel

March 4, 2013

It’s just been a couple of days since this add-in has been released and I can’t seem to stop using it. I guess I haven’t been this excited about a Microsoft offering since SSRS 2008 R2 (well, I am really passionate about Tabular and PowerPivot now but I wasn’t that hooked onto those technologies when they were released). As a BI consultant, blogger and speaker, I traverse through lots and loads of open data – sometimes trying to get that extra piece of open data which will add value to my clients’ existing data or to get some interesting stuff for my blogs and presentations. Data Explorer might not be the perfect solution, but it sure does a splendid job of reducing my efforts in searching, shaping and preparing my data. I thought of sharing my experiences in the form of a quick introduction to Data Explorer Preview for Excel. Introduction to Data Explorer Preview for Excel 1) What is Data Explorer Preview for Excel and where can I get it from? Microsoft “Data Explorer” Preview for Excel is an add-in for Excel 2013 which provides an intuitive user interface for data discovery, data transformation and enrichment. You can download the add-in from here. 2) Basic Tutorial for Data Explorer You can import data into excel from a wide variety of sources using Data Explorer and the complete list of sources is given here. For the purpose of this demo, I am going to be connecting to my favourite source which is the ‘web page’ source. a) After installing the add-in, you should be able to see the Data Explorer tab in Excel (if not, go to File—>Options—>Add-Ins—>Com Add-Ins and enable the Data Explorer add-in). Click on the From Web option and enter the url – as shown below. Import From Web b) Now click on Table 0 in the Navigator pane and you should be able to see the UFO sightings by states. The table has some non-US data and so to filter them, click on the dropdown in the reports column and unselect them. Click on OK when you are done. Unselect unnecessary data c) Rename the columns as States and UFO Sightings respectively. Notice that the Steps pane on the right shows the modifications you are making and you can expand the pane. rename columns d) Click on Done and now you can see the data in Excel. This data can be used as a source for your charts / tables and in the image below, I have used the “Geographic Heat Map” Office app to visualize the same. Looks like the aliens have taken a liking for California! Data visualized using Heat Map 3) Interesting Features a) Online Search:- I spend a lot of time trying to search for open datasets and this feature is surely going to reduce that time. You can search for data right from Excel by clicking on the Online Search button. For eg, if I search for ‘richest states’, the results as shown below. Online Search option Just scroll your mouse over the results and click on Use to add the data. b) Filter & Shape Data:- The ability to filter and transform your data is what makes this so useful. You can just click on the Filter & Shape button to start the process as shown below. Filter & shape data For eg, if you just want to show the income for 2011, you can hide the rest of the columns as shown below. Hide unnecessary columns You can also do a lot of other operations like Splitting columns, removing duplicates, replacing values, changing types, group-by, etc. Types of transforms available You can read the complete list from here. Hopefully, there will be more additions to this in the future (I could definitely use a Unpivot / Pivot option). c) Merging & Appending from Multiple Sources:- This is another killer feature in Data Explorer. You can merge or append from multiple sources. For eg, if we have to merge the two sheets that we just created, click on the Merge button and then select the primary and secondary tables from the dropdown. Merge data from two sources Then select the matching columns from both the tables and click on Apply. select matching columns Now, to display the additional columns, click on the expand icon on the New Column and select the UFO sightings. Click on OK. Expand columns Now you have combined both the sources and can use it for your visualization. I have visualized the same data using GeoFlow below. Data visualized using GeoFlow 4) Further Reading There’s lot of interesting stuff you can do with Data Explorer and if this post caught your interest, make sure to check the below ones too

1) Data Explorer Team – Announcing Microsoft “Data Explorer” Preview for Excel

2) Chris Webb – Importing Data From Multiple Log Files Using Data Explorer

3) Chris Webb – Calling A Web Service From Data Explorer, Part 1

4) Jamie Thomson – Traversing the Facebook Graph using Data Explorer

5) Matt Masson – Access the Windows Azure Marketplace from Data Explorer

6) Jake Smillie – Best Oscar winning Film? My first Data Explorer adventure…

7) Ian Morrish – SharePoint OData and the Excel Data Explorer

8) Data Explorer Help


9) Dan English – Installing Data Explorer Preview & Demo with IMDB Data

10) Alan Koo – Introduction to Microsoft Data Explorer Preview for Excel 2013 – Part 1

Posted by SQLJason, 1 comment