Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
#BI 39
BRH 39
#MSBI 29
SQL Server 28
#SSRS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

Unpivoting Data in Data Explorer

Mar 14 2013 7:26AM by Jason Thomas   

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.


Jason Thomas
19 · 9% · 2997


Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]