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…….

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:

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.

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.

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

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!

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)

• • https://www.digitalvidya.com/blog/power-bi-data-sources/
  • https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-connect-to-data
  • https://docs.microsoft.com/en-us/power-bi/service-get-data

• Perform transformations. May include: Design and implement basic and advanced transformations; apply business rules; change data format to support visualization
  • https://www.youtube.com/watch?v=brNB7XaLZYA
  • https://www.youtube.com/watch?v=pZ7FIW6yrjs
• Cleanse data. May include: Manage incomplete data; meet data quality requirements

• • https://www.blue-granite.com/blog/self-service-bi-improving-data-quality-using-power-bi-flow-powerapps
  • https://www.altentertraining.com/microsoft/power-bi/column-profiling-is-good/

Modeling and Visualizing Data

• Create and optimize data models. May include: Manage relationships; optimize models for reporting; manually type in data; use Power Query
  • https://www.youtube.com/watch?v=8ybsChtuZoY – basic data modeling
  • https://www.c-sharpcorner.com/article/creating-and-managing-relationships-in-power-bi/
  • http://radacad.com/what-is-the-direction-of-relationship-in-power-bi
  • https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance
  • https://powerbi.microsoft.com/en-us/blog/getting-started-with-power-query-part-i/   and https://powerbi.microsoft.com/en-us/blog/getting-started-with-power-query-part-ii/
• Create calculated columns, calculated tables, and measures. May include: Create DAX formulas for calculated columns, calculated tables, and measures; Use What If parameters

• • https://www.sqlbi.com/articles/start-learning-dax-for-free/
  • https://www.youtube.com/watch?v=nJSXty9Y4tM
  • https://www.youtube.com/watch?v=mEhYaXOeER0 – what if

• Measure performance by using KPIs, gauges and cards. May include: calculate the actual; calculate the target; calculate actual to target; configure values for gauges; use the format settings to manually set values
  • https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi
  • https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-radial-gauge-charts
• Create hierarchies. May include: Create date hierarchies; create hierarchies based on business needs; add columns to tables to support desired hierarchy
  • https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
  • https://www.jetglobal.com/blog/working-with-weeks-in-power-bi/
• Create and format interactive visualizations. May include: Select a visualization type; configure page layout and formatting; configure interactions between visual; configure duplicate pages; handle categories that have no data; configure default summarization and data category of columns; position, align, and sort visuals; enable and integrate R visuals; format measures; Use bookmarks and themes for reports
  • https://associationanalytics.com/2017/06/25/choose-right-visualization/ – select visualization
  • https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-best-practices#visual-types-and-best-practices  – select visualization
  • https://powerbi.tips/2018/01/introducing-layouts/ – layouts
  • https://docs.microsoft.com/en-us/power-bi/power-bi-report-display-settings – formatting
  • https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions configure interactions
  • https://docs.microsoft.com/en-us/power-bi/power-bi-report-copy-paste-page – Duplicate pages
  • https://www.youtube.com/watch?v=rsUMVJAIH00 or https://www.youtube.com/watch?v=0soi-uQTNb0 – handle categories that have no data.
  • https://www.sqlchick.com/entries/2017/11/22/why-the-default-summarization-property-in-power-bi-is-so-important – Default Summarization
  • https://docs.microsoft.com/en-us/power-bi/desktop-data-categorization – Configure data category
  • https://www.youtube.com/watch?v=jieMTFzEIfg – position, align and sort visuals
  • https://docs.microsoft.com/en-us/power-bi/desktop-r-visuals – R Visuals
  • https://community.powerbi.com/t5/Desktop/Convert-a-measure-text-into-a-percentage-format/td-p/486601 – example of formatting options with measures, using the format dax function.
  • https://www.youtube.com/watch?v=bU35LnSfo70 – bookmarks
  • https://www.archerpoint.com/blog/Posts/how-set-custom-color-themes-microsoft-power-bi – themes
  • https://docs.microsoft.com/en-us/power-bi/desktop-report-themes – themes
• Manage custom reporting solutions. May include: Configure and access Microsoft Power BI Embedded; enable developers to create and edit reports through custom applications; enable developers to embed reports in applications; use the Power BI API to push data into a Power BI dataset; enable developers to create custom visuals
  • https://docs.microsoft.com/en-us/power-bi/developer/embedding
  • https://www.youtube.com/watch?v=ZCXw6uri2L8 – use API to push data into dataset
  • https://docs.microsoft.com/en-us/power-bi/developer/embed-sample-for-customers#step-1-setup-your-embedded-analytics-development-environment
  • https://www.youtube.com/watch?v=Dt_gwJ6qeT0 – custom visuals

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

• Access on-premises data. May include: Connect to a data source by using a data gateway; publish reports to the Power BI service from Power BI Desktop; edit Power BI Service reports by using Power BI desktop
  • https://www.youtube.com/watch?v=nRJvTuEDFwk – Configure gateways.and adding data sources.
  • https://docs.microsoft.com/en-us/power-bi/desktop-upload-desktop-files – publish to service from desktop
  • https://docs.microsoft.com/en-us/power-bi/service-export-to-pbix edit PBI service reports using desktop.
• Configure a dashboard. May include: Add text and images; filter dashboards; dashboard settings; customize the URL and title; enable natural language queries
  • https://docs.microsoft.com/en-us/power-bi/service-dashboard-add-widget add text and images
  • https://powerdax.com/power-bi-dashboard-filters/ – filter dashboards (workaround)
  • https://docs.microsoft.com/en-us/power-bi/power-bi-report-add-filter – filters on reports
  • https://www.youtube.com/watch?v=MxmXYA_EAes – dashboard settings, enable Natural language queries
  • https://docs.microsoft.com/en-us/power-bi/service-dashboard-edit-tile Customize URL and title
• Publish and embed reports. May include: Publish to web; publish to Microsoft SharePoint; publish reports to a Power BI Report Server
  • https://www.youtube.com/watch?v=UF9QtqE7s4Y – publish to web
  • https://www.youtube.com/watch?v=dTEf9GW1BEc – Publish to SharePoint
  • https://docs.microsoft.com/en-us/power-bi/report-server/quickstart-create-powerbi-report – Publish to PBI Report Server
• Configure security for dashboards, reports and apps. May include: Create a security group by using the Admin Portal; configure access to dashboards and app workspaces; configure the export and sharing setting of the tenant; configure Row-Level Security
  • https://www.youtube.com/watch?v=gbSuFST-Nx4 – how to get a trial tenant so you can see the admin portal.
  • https://www.youtube.com/watch?v=q799ALXuF6o – tutorial that goes through all the admin portal functions
  • http://radacad.com/row-level-security-configuration-in-power-bi-desktop – Row Level Security
• Configure apps and apps workspaces. May include: Create and configure an app workspace; publish an app; update a published app; package dashboards and reports as apps
  • https://www.youtube.com/watch?v=Ey5pyrr7Lk8

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

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