Part 6 – Power View – Reporting Data Indeed!

 

First let me start off by apologizing for the tardiness of this blog; I invited everyone to dine on Power Query via the Indeed! Series and then got up and left the table and for that I’m sorry. I have recently quit my job; started / finished my first three month consulting gig, planned and starting hiking the Pacific Crest Trail from Mexico to Canada. My life has gotten a bit hectic, no excuses though; I should have topped of the Indeed! series before today; and now on to more Power Query.

In the last part of the Indeed! series we started refreshing our data on a regular basis to accumulate jobs based on criteria we were interested in. The next step is to start reporting on our data; to see the jobs that are available, where they are most prevalent and what the salaries are for those jobs. The file for this post can be downloaded here.

Create a new Power View report based on the data you have accumulated so far.

This will populate the Power View slide with data in the previous Excel page.

So the next thing we will need to do is to clear the data that was automatically populated. Simply uncheck the fields from the right hand pane.

Next I’d like to look at the number of jobs by the cities we have in our query. So we need to select city into our fields and the job key to create a count ( select the down arrow and select Count (Not Blank) ). This should create a two column table with a count of jobs by each city we have queried.

 

Raw data is interesting but not compelling and it takes time to sift through; a chart should make our data pop a bit more. A reasonable chart may be a pie for this particular count (other charts will also work such as a bar chart) . If we go to the design tab and select “Other Chart” and “Pie” it will convert the data over to a pie chart.

The new graph should look something like this:

We can further limit this graph by only viewing those cities with 5 or more jobs in the history of our accumulation; this will eliminate any outliers and give a better idea of where we have more opportunities. We can filter the entire view or just this chart; in this case I have chosen just the chart.

 

Los Angeles makes up a good portion of our chart; so we may wish to eliminate that as a criteria for our job hunt if we don’t want to work in downtown Los Angeles (traffic is a giant consideration for me when selecting a job and downtown is a 75 minute commute each way). If we select all and then de-select Los Angeles we can look at all the suburbs with 5 or more jobs.

 

This might be useful if we were looking for a new place to live and wanted to find those locations with a good number of choices for employment. Another useful bit of data might be the average salary offered in each of the cities. The first thing to do is copy the existing chart (select the chart and press Ctrl-C then Ctrl-V).

Next convert the chart to a stacked bar chart in much the same way we converted the table to a Pie Chart.

Next remove the count of jobs from the bar chart and put in salary. Instead of having it as a total sum; convert the value to an average. This will produce a chart with an average of salaries by city.

We can also sort the chart by salary range in the upper left hand portion of the chart.

The final chart should look similar to what is below and should interact with the previous chart. If you select a city in one chart the same city should now be highlighted in the other chart.

If we repeat the process, copy an additional chart, add average salary and convert it to a table we have a more complete view.

We can select a city from our original pie chart and see what the number of jobs are for a city and what the average salary of those jobs are. We also have graphical representations to see where these jobs fall within the entire spectrum of Los Angeles suburbs.

 

This gives us a fairly good idea about our job search, number of jobs available and how much we can expect to get paid by each city. There is still a lot of room to play with these charts. We could add the dates in as a filter and just search on the past week or add the “Search Criteria” to our filters and select only those jobs which are “TSQL” or “SQL Server 2012”. That really is the fun of Power View and Power Query and Power BI; playing with the data after you have started gathering it; what new insights can you find for your own job hunt?

So far we have written a query to accumulate data based on variables and created a report on the data we have accumulated. The report is interactive and might be a good tool for personal use or perhaps to illustrate job availability to your boss and ask for a raise. Is this report ready for prime time or to be released? In short, no. The Power Query code has no comments, the report is Americentric instead of more worldly and an automated refresh would be nice in lieu of having an Excel sheet open on our desktop all the time. In addition we could play with the model a bit and create three job categories based on salary, lower than our current salary, about equal or higher. We could also create an average salary that is limited to a whole number instead of umpteenth digits. There are a lot of minor modifications that could be made to make our job hunt even more useful; go forth and play with the data!

Overall we have come a long way from an empty excel sheet to an interactive report and I hope this gets you started down the Power Query and Power BI path. I also hope that it illustrates how data can be useful in your personal life as well as work, for job hunting, house hunting or any number of personalized applications. If you have any questions please leave them in the comments section and I will get to them as soon as possible.

Part 5 – Power Query API – Refreshing Data Indeed!

After a number of hours spent experimenting and searching I’ve fallen a little short on this post ladies and gents! I was looking a simple way for the non-programming public to refresh data in an excel sheet on a regular basis. In an Ideal world I would have liked to be able to give the Indeed! Excel file to anyone and say follow these steps to accumulate data for a job search. Alas; the simplicity I was looking for I could not find. So what I will cover in this post is the least technical way I could come up with to refresh a Power Query worksheet. Then I’ll go over how I imagine most reports or data marts will access Power Query’s accumulating data. As always the files we have been working with can be downloaded here. Continue reading

Part 4 – Power Query API – Accumulating Data Indeed!

This is the fourth part of the Indeed! Series and for me at least the most exciting part of the series! Executing parameterized queries to an API is great; but as someone who works in Business Intelligence I know that accumulating data and the insights garnered from evaluating data over time are invaluable. In Part 4 we will be merging the two concepts presented on Chris Webb’s Blog and Faisal Mohamood’s (or Devin Knight’s Blog if you prefer). All the workbooks for this series can be downloaded here.

Continue reading