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. 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 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. 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)
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])))
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”})
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)
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”)
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. The expression for the calculated column is given below
if [State] = “NC” then [NC] else if [State]= “SC” then [SC] else [FL]
Now your result should look like shown below 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 9) Also, have a look at how the actual query looks like in the 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
let
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”})
in
HiddenColumns
Remember to change the name of the table name when you use it.
