Uncategorized

“I’ll remember to update that code for next year” and other reasons you have 27 vague calendar reminders on January 1st

Happy January data friends!

If you’re like most of us out here, when you’re learning to use a new application and don’t know an elegant way to do something, you get it done by whatever means necessary. This can mean fun things like hard coding daylight savings dates knowing full well they change each year. *Raises hand*

“I’m working smarter”, you tell yourself. “It would take me another hour to learn to fix/test this when I can take 2 seconds in January and just move the dates around”.  And you’re not wrong about that part. WHY FOR THE LOVE did we choose the first Sunday after a month with an irregular extra day every four years to have some parts of some countries shift one hour for awhile?

So you happily shift that job off to “future you” until you are ringing in the new year with 27 vague calendar reminders on January 1st to ‘Update that thing for the new year’ that you set for yourself 8 months ago.

Whelp, because you’ve been avoiding it and likely need a refresher on stuff that could break your PowerBI reports in January, here’s a collection of timely hints (mostly because somewhere something I built while learning is now broken).

January is showing before December

You’re minding your own business when someone says your report is showing January before December. You scoff, because you’re super sure your field is a date data type and that would just be bonkers. But then, you click on your report and nearly shoot diet coke out of your nose to see…….

out of order
Bar Chart where month order is January – August – September…etc.

You are probably doing this because you aren’t using the drill down buttons and instead just deleting those parts of your date column out of the visuals pane. To fix it you can add the date field back in again. Here’s a Demo.

My guess is you were doing it so you didn’t have to use the drill down feature. It’s super easy to use once you get the hang of it. It’s such a common issue for my users that I put it on the help page of most of my reports.

Here’s an example of one of the help pages I have:

help page

See? You’re not the only one.

Multiple years of data getting lumped together by month

Ok so here’s the tricky thing. Once you found one report like that, there are probably other reports with that same problem, except that if you include data that spans multiple years, you’re probably not even going to notice it if you’re looking for out of order months.

Por Ejemplo:

Here’s two years worth of sales data, by units sold. See how in the Axis box, the Date field still has it’s year component? You can see December 2016 didn’t do so great, but December 2017 was amazeballs for Giant Lobster Claw Mittens sales.

2yearsalesright

If we take the year component out, it’s going to combine all of the January data, and put it together, all the February data and put it together, etc. Now December looks good, but you’re wondering why people are buying tons of mittens in May. 2yearssaleswrong

This might catch you off guard if you have reports that you have continually getting new data, probably on scheduled refresh, and didn’t think about multi year scenarios when you were building it.

In conclusion- go check your reports and fix them so they’ll work for multiple years. Or go turn on the recurring option on those January 1st calendar reminders.

Do you have any other January related things break? Let me know!

Con Mucho Am(ad)or

Liz

Uncategorized

Resources for the Exam 70-778: Analyzing and Visualizing Data with Microsoft Power BI Certification

My other working title: A big ‘ol stash uh links!

If you’re interested in getting certified on Power BI, Microsoft has an exam that will do just that!

The most important link on this whole this is the link to exam objectives. They should be your starting point, and here’s why:

Microsoft starts by writing exam objectives. They have to justify where the answer is for each question they write in the documentation. Then they write the official course, the exam guide, and the exam itself. The people who write the objectives may or may not be part of the group that write any of the other materials- including the exam!

PBI exam diagram

Why you should care: This means that any study material might not cover a detail that is on one of the exam questions. Also! Since the exam is updated more frequently than the the course or exam guide, buying a version that doesn’t correspond with the latest exam version could mean you learn something that has since been changed on the test.

The bottom line here is that you should focus on your ability and knowledge of the exam objectives to be ready for this certification. And read the documentation from Microsoft.

With that said, here are a bunch of links and a possible study plan intended to facilitate your doing that.

**Disclaimer: I don’t vouch for the completeness of covering over all the details included on the exam. (see above for why I think that would be impossible) I also don’t get any kickback from any of these sources. I tried to choose materials that a complete beginner would be able to understand, and that has links where applicable to learn in greater detail. I also tried to choose a variety of sources to expose you to just how many great people are putting content out there for you to follow for future use. Sometimes the most clear answer was to jump into the Microsoft documentation. The context of this list is for the exam version dated July 31, 2017.

Possible Study Plan:

  1. EdX Course – Analyzing and Visualizing Data with Power BI
  2. Read through all the documentation
  3. Click all the buttons and know what they do in PBI Desktop (including power Query), Power BI Service
  4. Learn about the admin portal – here is a tutorial on how to get a trial tenant:
  5. Read through all the objectives, make sure you understand each one, and common mistakes one could make.
  6. Do a deep dive on google for tutorials on subjects that you haven’t had experience with yet, or don’t have access to in your org (like the admin portal!)

 

And now for the exam objectives themselves!

Consuming and Transforming Data By Using Power BI Desktop

  • Connect to data sources. May include: Connect to databases, files, folders; import from Excel; connect to SQL Azure, Big Data, SQL Server Analysis Services (SSAS)

Modeling and Visualizing Data

Configure Dashboards, Reports, and Apps in the Power BI Service

I’ll be editing this list soon to make it easier to read, I’m just airport hopping at the moment trying to get home. 🙂

Con Mucho Am(ad)or,

Liz

all the sorting options in the post side by side
Power BI Tips, Uncategorized

Sorting Bar Charts Power BI Workaround

It comes up at every PowerBI conference I attend.

a particularly magnificent horse
Horses are magnificent. Let’s not beat them to death.

I was in a business luncheon at the Power BI World Tour Event in Seattle this week where THE Will Thompson said it was a ‘known issue’. But in playing around with it, I found a possible workaround that might save you from having to explain to your report consumers thinking ‘that should be fairly simple to do… right?’, that in fact, no.

Let’s get to it. Here’s how to sort columns on a bar chart.

Say you start with some data. I made sure neither column was in any particular order.

Table of 5 kinds of pies and a random value between 0 and 100 for each as their popularity

Here’s what happens when I make a bar chart out of it:

Bar chart sorted by popularity, descending

I’m assuming PowerBI has some kind of default logic to sort these. From what it did here, it might be by amount.

SOLUTION: Sort the data in your table immediately before creating your visual. To do this, go to the data view on the left side of the application. Then click Modeling > Sort by Column > choose which column you’d like to have your bars sort on.

screenshots of the steps described

RESULT:

I sorted by data by “Pie” (A-Z), then made a visual and I got:

bar chart sorted by pie name alphabetically

Boom!

The takeaway here is that you have to get your data sorted BEFORE you create the visual. If you change the sorting on your data later, it doesn’t affect the ordering of your bars.

And just because we can, let’s do all the possible combinations. Such Zen.

A comparison of the bar chart with default sorting, sorting by pie name, and sorting by popularity.

What else are you stuck on? Let me know and I might use it as an idea for a future post!

Con Mucho Am(ad)or,

Liz

Uncategorized

Welcome :)

profile pic of curly haired woman

Hi! I’m Liz Amador. I’m a data analyst. I use Microsoft’s Power BI. I founded a user group for women. I blog, tweet and speak at conferences. I work on projects for social justice. If you have a problem with a report, I want to help you solve it. Head to my about me page for more deets.

I hope this blog helps you with something, weather it’s a tip with Power BI, a place where you learn about how data in the real world is collected and handled, or opens your horizons to do more in the social justice world. I’d love to hear what you think.

I dedicate this blog to my mom. She’s also a data analyst, and despite my dad’s best pushing to become an engineer, I kind of naturally fell into loving data and spreadsheets<3. My mom is my biggest fan and supporter and there’s not a dance fad she won’t practice in public to embarrass me.

Love you, Mom.

 

Con Mucho Am(ad)or,

Liz