Category: Blog

Power Query – Nested If/And/Or Statements

Gerard DugganLeave a comment

This is a brief follow on to my earlier blog on the Maven Analytics NYC Taxi challenge, and cleaning well over 25 million line items of data in Power Query, before developing a Power BI dashboard. In particular, I am looking at amending an “If/And” statement to become a nested “If/Or/And” statement.

One of the cleaning steps involved looking at values in multiple columns to check that if they were ALL negative, then we should change each value to a positive (multiply by -1).

Original M Code

To do the above, my original M Code involved adding a new column which returned a statement based on a set of IF/AND statements

= Table.AddColumn(#"Changed Type", "negative_charges", 
each if [fare_amount] <0 and 
[extra] <0 and 
[mta_tax] <0 and 
[improvement_surcharge] <0 and 
[congestion_surcharge] <0 then 
"all negative" else 
"ok")

This effectively adds a new column called “negative charges”, with each row value being returned as either “all negative” or “ok” based on the if statements.

So, if the fare amount, extra, mta tax, improvement surcharge AND congestion surcharge were ALL negative for that particular journey, then the result would be “all negative”.

However, if one or more were greater than or equal to zero, they would be deemed “ok”. This would allow us to then amend values in rows which were only calculated as “all negative”.

Minor Problem

It was pointed out to me however, that the [congestion surcharge] may also contain ‘null’ values. These would not be considered in the above calculation. Typically, we may consider ‘null’ to equal zero, therefore it would not matter; however for these surcharges, ‘null’ may mean that the surcharge was not being applied at that time.

Therefore, I had to look at amending the M code to accommodate this.

Updated M Code Solution

In the end, the amendment is not so complicated, but you can create a nested conditional statement by wrapping it in parentheses – see below amended M code. This allows you to check for multiple Or and And conditions and return the appropriate result.

= Table.AddColumn(#"Changed Type", "All Negative Check", 
each if ([fare_amount] <0 or [fare_amount] = null) and 
([extra] <0 or [extra] = null) and
([mta_tax] <0 or [mta_tax] = null) and
([improvement_surcharge] <0 or [improvement_surcharge] = null) and 
([congestion_surcharge] <0 or [congestion_surcharge] = null) then 
"all negative" else 
"ok")

If you want to see this in action, I have created a short walk through on my youtube channel here.

5 Quick Tips for next level BAN in Tableau

Gerard DugganLeave a comment

Everyone loves a BAN (Big Aggregate Number). They are your all important key numbers in your dataset and should be jumping off the screen and and ingraining themselves in the back of your retinas!

But the standard way of creating them in Tableau can be a bit dull and monochrome.

Below I’ll step through the usual way of creating a set of BAN, followed by 5 quick tips to take them up a level in a recent dashboard I created as part of a Maven challenge

Standard BAN Creation

Normally, you will have a relatively small set of categories that you wish to show an aggregate value for (example being “Age Group” below.

We traditionally create this by dragging the category (Age Group) into the columns, then pulling the calculation into Text marks area.


Voila! We have a BAN. Not very pretty, but a BAN all the same.

You can adjust the header and value for font type, size and colour. Normally, that is about as far as most people go.

What’s the alternative?

But hey – what if you want your BAN in a single horizontal or vertical line, or you want to colour code based on the category or value?

What can we do to customise our BAN and make it that little more memorable?

5 Simple Tips to go from “Boring BAN” to “Badass BAN”

1. Orientation

It is easy to change from a single horizontal to a vertical line, by simply dragging the category from column to rows. This allows you to maximise your data real estate depending on how how you are structuring your overall visuals.

2. Headers

We can drop a duplicate category “Age Group” onto the Text marks card. Then right click on the category header and select remove. This will result in the second image below – still not too pretty, but we are on our way.

You can keep the category above or below the BAN by shifting it up or down on the marks card.

However, my preference is to keep it below, as it keeps the focus on your Big Number!

3. Font

Many people may have different views, but my preference is to keep a single font on a dashboard. Having multiple fonts can become an unwanted distraction and give a clunky look.

4. Size

For the numbers, bigger is better!! Make the size of the numbers much larger than any adjacent text to emphasize the contrast.

To do this, select the Text icon in the marks card, and click on the three little dots on the side.


This will bring up the “edit label” input box. Here you can adjust the size and font attributes (bold, underline, italic).
In my example, I chose 36 for the Aggregate Number and 16 for the underlying category.

5. Colour

Adding some colour can help place emphasis on numbers or categories, and help improve the aesthetics and feel of your visual.

Ctrl dropping the category onto the colour mark would allow you to assign distinct colours based on each category, whereas Ctrl dropping the calculations “CNT(Consumers)” onto the colour mark will allow you to assign colour.

You can also maybe apply a quick table calculation. Here I opted to show a % of total, rather than the straight numbers. This gives a good overall perspective. As shown in the below picture, you can right click on the aggregate number and select the quick table calculation.


Lastly, in my example, I opted for adjusting the colour based on the BAN value itself. As I wanted to draw the eye to the highest value, I used a diverging scale from a green (#00aa00) to a white, which was offset at -20%. This enabled my lowest value to still be almost visible, while keeping the focus squarely on the largest number.

Overall

I was pretty happy with the outcome, and was able to apply the same effect to two sets of BAN. This helped maintain the overall important consistency and look when they were brought into the main dashboard.

What do you think?

As always, if there are any questions or comments, please reach out. I am happy to help where I can, and always open to feedback on alternative methods and learning new tricks from the data fam.

Happy Analysing – DG

Maven Challenge – Mexican Restaurant Scouting

Gerard Duggan1 Comment

It was that time of the month when Maven Analytics set their data playground challenge – #mavenrestaurantchallenge. This time it was using 5 separate csv data tables detailing consumer and restaurant data from cities across three states in Mexico during 2012.

The brief was to

  • assume the role of a Lead Analyst that scopes out new restaurant opportunities abroad
  • review and analyse the data provided for interesting patterns and trends
  • develop a single page visual that gives potential investors a good understanding of the market to guide them in making investment decisions
  • post your final proposal on LinkedIn for review by Maven and fellow challengers

My Plan

As the final product was going to be a single page (jpg) being viewed on LinkedIn, it makes sense to keep the visual:

  • Simple and clean with a consistent palette
  • Structured and flowing, telling a progressive story
  • Clear in terms of visibility of graphics and legibility of any text

I landed on asking three simple questions:

  • Who? – who were the key demographic to target in terms of age and smoking/drinking habits
  • Where? – which city or location had the highest ratio of consumers to restaurants
  • What? – what kind of cuisine should a restaurant serve based on popularity and levels of current availability

My Solution

I decided to use Tableau for this challenge to try out some newly acquired skills. The below was my final proposal posted on LinkedIn, as well as on my Tableau Public Account

I will share some tips on how I made a few of the visuals in a follow up blog. If anyone reading has any queries on any part of the visual or it’s development, drop me a comment, and I’ll be happy to provide further detail.

Happy Analysing!

Welcome to DG Analysis

Gerard DugganLeave a comment

Hello, and welcome to DG Analysis, a look into the varied world of data analysis and the tools used to present it.

My interest in data analytics has spawned from a ‘side-hustle’ at work which has led me from the basics of simple excel spreadsheets to the world of Power BI DAX and Tableau, and most things in between.

Photo by Pixabay on Pexels.com

Join me in looking as I take part in various online data analysis and visualisation challenges, and share my strategies and processes as I go, as well as any tips and tricks I can impart along the way.