Presented at #sqlpass summit 2015.
[VIDEO] Microsoft’s vision for “Advanced analytics” (presented at #sqlpass summit 2015)
StandardPresented at #sqlpass summit 2015.
Presented at #sqlpass summit 2015.
Power Query is amazing! It takes the data analysis capabilities of Excel to whole new level! In this post, I am going to share three reasons:
Have you every had to do your data analysis tasks repeatedly on the data with same structure? Do you get “new” data every other week and need to go through the same data transformation workflow to get to the data that you need?
What’s the solution? Well, you can look at MACRO’s! Or you can request your IT department to create a Business Intelligence platform. However, what if you need to modify your data mashup workflow then these solutions don’t look great, do they now?
Don’t worry! Power Query is here!
It enables repeatable mashup of data like you might have never seen before! You need to try it to believe.
It’s very easy to input new data to Power Query and it enables you to retrieve final output based on new data using a “refresh” feature.
Each data-mashup is recorded as steps which you can go back and edit if you need to.
Any data mashup performed using Power Query is expressed using its formula language called “M”. You can edit the code if you need to and as you can imagine such a platform enables much-needed flexibility for the analyst’s.
Do you want to Merge data? How about Join? Are you tired with VLOOKUP’s! Don’t worry! it’s super easy with Power Query! Here’s a post: Join Excel Tables in Power Query
How about Pivot or Unpivot? Done! Check this out: Unpivot excel data using Power Query
How about searching for online & open data sets? Done!
How about connecting to data sources that “Data” section of Excel doesn’t support yet? (Example: Facebook) – DONE! Power Query makes that happen for you.
And That’s not a complete list!
Plus you can unlock the “Power” (pun intended) of Power Query by using it with other tools in Power BI Stack. (Power Pivot, Power View, etc…) OR you can use the your final output from Power Query with other tools too! After all it’s an excel file.
If you haven’t already then check out Power Query! it’s free and works with Excel 2010 and above.
Author: Paras Doshi
I was live tweeting during our monthly PASS Business Analytics VC meeting, Here are the tweets to learn about 13 Excel Tips!
130+ folks already logged in for the webinar! It's not too late to Join! 13 #excel tips: http://t.co/deXvWxFw45 #sqlpass #sqlserver #msbi
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #1/13 – What if / Goal Seeking. #excel #sqlserver #sqlpass http://t.co/135Gu5irmt
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #2/13 – VLOOKUP #excel #data http://t.co/135Gu5irmt
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #4/13 – Pivot Table against data sources (OLTP/OLAP). #excel #olap #sqlpass http://t.co/135Gu5irmt
— PASS BA VC (@passbavc) November 13, 2014
Kevin Goff (@KevinSGoff ) demo'ing some lesser known Pivot Table features: visual slicers, sorting & "show values as" features. #sqlpass
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #3/13 – Named Ranges in Excel –@KevinSGoff #excel
— PASS BA VC (@passbavc) November 13, 2014
Nice tip for #Excel Pivot Tables: use filters when you have long list (customer names). use slicers if you have a short list (5 countries)
— PASS BA VC (@passbavc) November 13, 2014
Tip #6/13 – Pivot Charts with Sparklines. #excel #sqlpass
— PASS BA VC (@passbavc) November 13, 2014
Tip #5/13 – Pivot Table options: Implementing Top and ALL OTHER in Pivot Charts/Tables.
— PASS BA VC (@passbavc) November 13, 2014
Tip #7/13 – Highlighting High Point & Low point in sparklines. #excel #sqlpass pic.twitter.com/91k3Ce2ULj
— PASS BA VC (@passbavc) November 13, 2014
Kevin Goff (@KevinSGoff ) demo'ing using conditional formatting & macro recording. #excel #sqlpass http://t.co/deXvWxFw45
— PASS BA VC (@passbavc) November 13, 2014
Tip #8/13 – Power Pivot! #excel #powerbi #sqlpass #powerpivot <- A must try tool for all data professionals!
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #9/13 – KPI's and DAX formula's in Power Pivot. #powerbi #sqlpass #excel http://t.co/2WcWDqSJEP
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #10/13 – Power View #sqlpass http://t.co/2WcWDqSJEP
— PASS BA VC (@passbavc) November 13, 2014
Keving Goff comparing Power View, SSRS and Excel Pivot Tables/Charts features for reporting/dashboard. #sqlpass #excel
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #11/13 – Want to write custom MDX in Pivot Tabels? Check out: http://t.co/9RkrGSNr0J #sqlpass #excel #olap #ssas #mdx
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #12/13: A special pivot chart: pic.twitter.com/2DPspZdsUm
— PASS BA VC (@passbavc) November 13, 2014
Excel Tip #13/13: Follow @Technitrain @marcorus @FerrariAlberto blog's for #Excel, #DAX, #PowerBI & #PowerPivot, etc. #sqlpass
— PASS BA VC (@passbavc) November 13, 2014
Thanks everyone who attended, I hope it was helpful!
Here are some ways to follow the Virtual Chapter:
Website: http://bavc.sqlpass.org/
Youtube: https://www.youtube.com/channel/UCOiRAA4gBxEeVxwmEZ1qy1w
Twitter: https://twitter.com/passbavc
LinkedIn: https://www.linkedin.com/groups/PASS-Business-Analytics-Virtual-Chapter-6701113
Power BI is an exciting new technology in the business analytics space from Microsoft. I’ve played with its current preview version & attended couple of sessions on Power BI at PASS Summit 2013. Based on my first impression, I noted down Problems that Power BI solves. Note that as of today, it’s in preview & so information around cost is not availale yet but I try to learn and understand as much as I can Today about how Power BI is going to help business users & power users in the future. As a part of that, I’m attending Business Analytics VC’s session on “Power BI Info Management and Data Stewardship” by Matthew Roche & Ofer Ashkenazi on Nov 7th 12 PM EST.
Topic Abstract:
“Business intelligence tools continue to improve, letting users shorten their time to insight and take that insight to more devices in more places. But this evolution of BI doesn’t change one fundamental fact of information management: You can’t gain insight from data you can’t access.
In this session, Matthew Roche and Ofer Ashkenazi will introduce the role of the data steward and the self-service information management capabilities included in Power Query and Power BI for Office 365, focusing on how Power BI empowers business users to add value to the organization.”
Closing note:
I recently volunteered at Business Analytics VC as VP of Marketing, so it’s in my interest to spread word about the event but I would not spread word about something unless it gets me personally excited about it! 🙂
I hope to see you at the session and for some reason if you can not make it, we usually record sessions & so you can check out the meeting archives section of the PASS BA VC site after the event.
Problem:
There’s an excel 2010 pivot table that’s getting its data from TestServer. How do I change the data source so that the Pivot Table get’s it data from ProdServer?
Note: This is a common scenario among Business Intelligence Developers who might want to switch between different servers (Test/Prod) to for comparing data.
Solution:
1. Establish the connection to a NEW data source via Data Tab in Excel.
2. Select the Pivot Table whose connection needs to be changed.
3. From the Excel Toolbar. Go to Pivot Table Tools > Options > Change Data Source
4. Choose the NEW connection from here. (you can use the connection that you created in step #1)
5. Click OK and that should have successfully changed the data source for you. Please test the column names/values that might have been affected since you changed the data source.
That’s about it for this post. Your comments are very welcome!
Pivot Tables are powerful mechanism to analyze data. And as you may know, it’s really popular among business professionals that use excel for their day-to-day data analysis needs. With that context, Here’s the blog post on Pivot Tables Layout:
I got a question from my client who had a specific need on how she wanted her Pivot Tables to look. Here’s how the Pivot Table should look like:
I am using Excel 2010 Pro Plus to demo the solution, please use this solution to adapt to use it with other versions of excel.
Without formatting and changing the layout, the excel pivot table looked like this:
Now, let’s work step by step to meet the requirement:
Step 1: click somewhere on the Pivot Table and from the Toolbar, Switch to the Design Tab under PivotTable Tools
Step 2: Now here, Go to Report Layout > Show in Tabular Form
Step 3: As you might have noticed the “hierarchical” structure is now broken up into multiple structure getting us closer to meet the requirement.
Step 4: In our requirement, you can see that it does not give the user to see the “+” or “-” (expand, collapse) buttons. So, let’s hide these buttons from the report from Step #3
Right click somewhere on the pivot table > PivotTable Options > Display tab > uncheck the box that says “Show expand/collapse buttons“:
Step 5: so now it looks as follows:
So as you can see, it meets our requirement now.
To recap, Here’s what we had to do:
#1: Change the Layout of the Pivot Table
#2: We saw how to hide the expand/collapse buttons too.
That’s about it for the post! Thanks for reading.
your comments are most welcome!
Introduction:
Data Explorer add-in is amazing! It’s helps you: combine, find and re-shape your data in Excel 2010/2013. I’ve blogged about: 1) How to merge Table Data and 2) How to clean duplicate data and now in this blog post, I want to share a step-by-step on Unpivoting data using the Data Explorer add-in.
Before we begin, If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx
Problem:
What is un-pivoting? I hear you ask. Instead of explaining it, let me share an Image:
BTW, the above data is from my Facebook Page Insights.
So our problem statement is (please refer to above Image): we are given table blue and we need to output table green. In other words, we need to Unpivot the data.
Solution:
Here are the steps:
1) Open Excel, Open Data Explorer add-in. And Connect to your data. Wait when you see the Query Editor.
2) (Optional) In the Query Editor, Rename the query. I renamed it to “Unpivot Data”. And this how my query editor looks:
3) Now, Select the columns that need to be unpivoted > Right Click > Unpivot Column
Note that I’ve selected all columns that I want to UnPivot:
4) You’ll see the updated results in the query editor window. I renamed the columns “Attribute” to “Age and Gender” and “value” to “reach”. If you want to rename the columns, select the column > Right click > rename.
If everything looks OK, click on Done in the bottom right corner
5) There you have it, Unpivoted data in Excel 2010/2013 using Data Explorer add-in!
And then its super easy to create charts, Here’s one I created after I had unpivoted the data:
Insight: For my blog, my Target Audience seems to Male between the age of 18-24 and then 25-34.
FYI: The Date Range of the Data Set of 1st Jan 2013 – 25th Apr 2013.
That’s about it for this post, Here are some Related articles:
Your comments are very welcome!
Problem:
Merging/Joining/Combining data-sets in Excel has not been an easy task. There are third-party add-ins that makes it easy but out of the box, excel didn’t have an easy way to merge/join table data. But now with the Data Explorer add-in, we have an add-in that let’s us merge/join data in excel w/ few clicks.
If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx
Situation:
Input is Table 1 & Table 2. The output we need is merged Table.
Table 1:
Date | Daily New number | Month |
1/1/2012 | 0 | 1 |
1/2/2012 | 0 | 1 |
1/3/2012 | 0 | 1 |
1/4/2012 | 0 | 1 |
1/5/2012 | 0 | 1 |
1/6/2012 | 0 | 1 |
1/7/2012 | 0 | 1 |
1/8/2012 | 0 | 1 |
1/9/2012 | 0 | 1 |
………………………………..
Table 2:
Month | Month Name |
1 | January |
2 | February |
3 | March |
4 | April |
5 | May |
6 | June |
7 | July |
Merged Table:
Date | Daily New number | Month | Month Name |
1/1/2012 | 0 | 1 | January |
1/2/2012 | 0 | 1 | January |
1/3/2012 | 0 | 1 | January |
1/4/2012 | 0 | 1 | January |
1/5/2012 | 0 | 1 | January |
1/6/2012 | 0 | 1 | January |
1/7/2012 | 0 | 1 | January |
1/8/2012 | 0 | 1 | January |
1/9/2012 | 0 | 1 | January |
………………………………
Solution:
Let’s see how data explorer can help us Join/Merge Table 1 & Table 2.
1) create query that connects to Table 1 & Table 2.
2) Once you have queries that connect to the tables need to be merged, then click on Merge
3) Once you click on Merge, you’ll see a dialog:
Here you need to configure three things:
a) First Table
b) Second Table
c) Columns that will be used to merge/join data
In this case, this is how my merge dialog looks:
4) Once configured correctly, click on OK. You’ll see a dialog box where you can configure the output of the merged table. click on the new column to see the options that are available to you to configure the output of the merged table:
5) In this case, I’ve selected just one column month name that needs to be merged. You can also explore the aggregate tab in case you’ve numbers that needs merging.
6) This is how the output looks:
7) Rename the new column.
Select the new column > Right Click > Rename
8) Click Done if it looks OK.
9) The merged data is now available to you in Excel!
And one can analyze it!
Let’s see before and after. Note that instead of month numbers, we now have month names
In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.
In this blog post, we’ll see how you can remove duplicated and clean data in excel tables using Data Explorer Add-in.
Problem:
Our Excel Table has following Data:
Month | Month Name |
1 | January |
1 | January |
1 | January |
2 | February |
2 | February |
3 | March |
And we want to remove duplicates to make the data-set look like this:
Month | Month Name |
1 | January |
2 | February |
3 | March |
In real world data-sets, we wouldn’t have few rows but lot’s of rows and doing it manually wouldn’t be the wisest option. With that in mind, let’s look for a few-clicks solution that can help us remove duplicates.
Solution:
If you haven’t already, download the Data Explorer add-in preview available for Excel 2010 & 2013. It can do a lot more than removing duplicates – it’s a great add-in and it’ll save you lots of time especially if your job involves discovering, cleaning and combining data for analysis purposes. After you’re done installing the add-in, use the steps below to remove duplicates in an excel column:
1. Open Data in Excel. Switch to Data Explorer Tab
2. For the purpose of the demo, I am assuming that you already have the data in excel file. If not, you can connect to other sources via the add-in.
3. Data Explorer add-in > Excel Data> From Table
4. After you’ve clicked on the From Table, a query editor will pop up:
5. Select both columns
(you can select both columns by: select first column > hold down the ctrl key and then click on second column)
6. Right click > Remove Duplicates
7. click on done if you see that the duplicates have been removed correctly
Conclusion:
In this blog post, we saw how to remove duplicates and clean data in Excel using the Data Explorer Preview add-in.
If you’ve not downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx
Note:
1) URL to download the add-in may change in future
2) The steps that I described may also change because as of today the ad-in is in “preview” stage and things may change in future.
I was playing w/ a time series data set in Excel 2010 and learned how to add a Trend-line and in this blog post, I’ll share how I added it:
First up, How is Trend-line useful? Here are few answers:
– It helps us see how data is changing over time, in other words, it helps us find “trends”
– It helps us forecast future.
With that, here is the chart without Trend-line:
Now let’s add the trend-line and you’ll be able to compare on your own how Trend-line makes it easier to spot “trends”. Here are the steps:
1. select the line > right-click > add trend line
2. configure the trend-line options
3. I also changed the line style
4. And Here’s the chart w/ trend-line
Conclusion:
In this post, we saw how to add trend-line in the time series chart in excel 2010