Extract Data from a Mixed Column

More and more I’m seeing examples where people are trying to extract data from a mixed column.  In other words, they have two data types in a single column, but need to find a way to extract one from the other.

Examining the issue

The sample data I’m using can be downloaded from this link.

I’m going to use Power BI Desktop for this, but the results will look identical in Excel using Power Query (except for the colour, of course.)

So let’s get started:

  • Get Data (new Query in Excel) –> From CSV –> MixedDataInColumn1.csv
  • Promote First Row as Headers

The issue can be seen in the red circles below… the report author injected the name of each vendor for the parts above their first part in the list.

image

So the issue here is how to extract the vendor name from Part No column.  The problem is that there isn’t any obvious way to do this.  We have different textual values in all columns, which could change over time.  There’s really nothing that we can test for reliably in this case.

How to Extract Data from a Mixed Column

There are actually a few different ways to extract data from a mixed column… a few of which we demonstrate in our Power Query workshop.  I’m going to show just one here.

Step 1 – Identify a column with a pattern you can exploit

The key we are really looking for is a column which has values or dates for all rows other that the one with our vendors.  In this case we actually have two: Part No and Cost.  Both have text on the Vendor lines, but what looks like values on the rest.  The challenge we have here is that we can’t always guarantee that Part No won’t have text in it.  It’s completely possible we could see a part number like TH-6715 or something. So this leaves us with the Cost column.

Step 2 – Duplicate the identified column

This next set of steps is actually the trick that lets us work this out.

  • Right click the column in question and choose Duplicate Column
  • Right click the Cost – Copy column –> Change Type –> Whole Number
  • Right click the Cost – Copy column –> Replace Errors –> null

You should now have null values where the textual values were located:

image

Step 3 – Use a little conditional logic

We now have something that we can use in order to extract the Vendor name.  So let’s build a little bit of conditional logic:

  • Add Column –> Conditional Column
  • Configure the Conditional Column as follows:

image

The only trick here is to make sure you change the Output to a column so that you can select from the list of columns.

  • Click OK
  • Right click the Vendor column –> Fill Down

The result is shown below:

image

Step 4 – Clean up

We’re now at the point of clean up which entails:

  • Filter the Cost – Copy column to remove null values
  • Delete the Cost – Copy column
  • Set the data types on all columns

The results now look as follows:

image

At this point we can commit the query and we are good to go.

Final Thoughts

This is not a new trick by any means; I’ve been using it for a long time.  The biggest key is really about identifying patterns and thinking outside the box.

It’s unfortunately very easy to get focused on the primary column we want to solve, and lose site of the others.  (Trust me, I’ve been there too.)  Sometimes though, when a column is particularly tough to deal with, we need to just step back and take a look at the bigger picture to see if there is a pattern in another column that we can exploit.  In fact, I’d say that this is probably one of the most important things to master when working with Power Query.

Visual Interactions in Power BI

In this post I’m going to explore the options for Visual Interactions in Power BI … in other words, I’m going to explore the options to control what happens to other visuals when you select one in Power BI.

Visual Interactions in Power BI – The default experience

Let’s take a quick look at a report and see what happens when we select a visual.  Here’s a simple report with 3 charts and a card:

image

And, when we click one of the visuals, it cross filters each of the others.  In the case below, I’ve clicked “Vancouver” in the “Course Attendees by City” visual, and it has cross filtered all the rest:

image

Okay, so no secret there.  The important things to remember here are that:

  1. I didn’t need to do anything to set up the linkage for the visual interactions in Power BI, and
  2. The cross filtering is applied to show the currently selected portion of the whole

But what if we didn’t want this?

The 3 Options for Visual Interactions in Power BI

There are actually three different states for visual interactions in Power BI:

  • Highlight (the default experience of cross-filtering with shading)
  • Filter (cross-filtering to show the contextual values only)
  • None (do not filter)

You can find each by selecting any visual on your report in Power BI Desktop, then go to Visual Tools –> Format –> Edit Interactions.

Let’s take a look at each of them.

Visual Interactions in Power BI – Highlight

As mentioned above, this is the default of the visual interactions in Power BI.  You don’t need to set up anything to get this behaviour.  If you monkey with it, however, you can get back to it by selecting a different visual, then clicking the little pie chart icon that appears above the visual you want to modify.

image

The only other thing I want to call out here is what happens when we select a set of data that filters all records out of another visual.  In the case below, I’ve selected Kelowna in the Course Attendees by City chart.  As you can see only four of our courses have been led in Kelowna:

image

Notice that the last three courses still show, even though we never ran them in this city.  Why?  Because the visuals indicate that we’ve led all our other courses somewhere, but obviously not in Kelowna.

Visual Interactions in Power BI – Filter

The next icon to the left of the pie chart is the Filter icon.  This toggles the visual slightly:

image

The key difference here is that the shaded portion is gone.  This gives the appearance of drilling in to the data a little more, without preserving the concept of how this data relates to the whole.

Now, check out what happens when we select Kelowna:

image

The Courses Run by Name visual no longer holds any data about the whole, allowing it to remove the irrelevant courses.  End result here is that we’re able to focus on the data that exists in this context only, without contaminating it with irrelevant data.

To be fair, most of the time I actually quite like the version with the shaded values.  But if you have a long list of data then this can certainly help trim it down so you don’t have to scroll the visuals as much.

Visual Interactions in Power BI – None

The last method we can configure for visual interactions in Power BI is to set the filter behaviour to None.  This prevents any filtering from taking place on a visual with this property set:

image

At first this looks quite similar to the Filter setting, but the key here is that the data in the Courses Run by Name visual has not been filtered at all, unlike the other two chart visuals and the card visual.  To display the effects just a bit further, the image below shows the card visual set to None, and the city filtered to Kelowna:

image

Notice that this time the Attendees card shows the true total for All Attendees.  The Courses Run by City visual, however, is filtering, as I left this in “Highlight” mode.

A key observation

I haven’t called this out yet, but should: we can set up different actions for each visual on the report.  That adds a fair amount of flexibility in order to get your report filtering working just the want you want.

A weird Visual Interaction

Before you look at the next visualization, I want you be keenly aware of this fact:

All visual interactions we set up as shown in the last image above.  I changed nothing else.

Keeping that in mind, look what happens when I click on the Creating Vibrant Dashboards course:

image

It cross filters all other visuals using the default interactions!

This is kind of a key thing to be aware of.  Just because I customized the visual interactions for the Course Attendees by City visual, it doesn’t force those relationships back the other way.  This means that I can customize how each visual affects the rest of the visuals on the page.  How cool is that?

The one missing setting

There is one setting that is badly missing, and that is the ability to persist selections when you select multiple visual filters.  What I mean by this is that I should be able to click on Vancouver, then click on the Creating Vibrant Dashboards selections to select only records that meet both of those criteria.  Alas there is no setting to do this today. You can set this up using drill down, but this means you need to think about what the user wants in advance and build it out, which is pretty tough if you have lots of potential filter combinations.

Using Aggregate to Count Visible Rows

In this post I’m going to show one of my favourite financial modeling tricks: how to use Aggregate to Count Visible Rows.

Background

Often, when I’m building models in Excel, I like to group key assumptions at the top of the worksheet in one area. This allows me to change them easily from a centralized location. The problem is that sometimes I need to collapse them to see more of the model.  Of course, you can use this trick to collapse any block of rows (or columns) in your worksheet, so it’s applicable to all kinds of uses.

Let’s take a look at the basic setup:

image

So it’s essentially a block of cells to capture key rates and stats.  No secret there.  And on the left I’ve added some outlining so that I can collapse it easily.  To do that we simply select rows 3:6 and go to Data –> Outline –> Group.

The Trick in Action

Now, check this out… I click the – on the left, and the rows collapse.

image

But check out the message in cell A7.  It wasn’t there before, but now we’ve got a nice message that not only tells you there is an area that is collapsed, it also leads the user as to how to show the rows again.

Using Aggregate to Count Visible Rows

The trick to this is using the AGGREGATE function (which works in Excel 2013 or higher).  So let’s check out how this works.

As AGGREGATE gives us back a count of rows, we will be able to test if the number of visible rows equals zero, and the react to it using an IF function.  So let’s get started.

AGGREGATE's first parameter: the Aggregation Type

=IF(AGGREGATE(

When we open the parenthesis, we are prompted for the first parameter.  There are a variety of options here, but the one I want is COUNTA(), which allows us to count the number of completed cells (either text or values):

image

Next up we put in the comma and we’re on to the second parameter.

AGGREGATE's second parameter: What to aggregate

image

Aha!  So using 5 will allow us to apply the COUNTA(), but ignore any hidden rows.  So it’s this parameter here that allows us to use AGGREGATE to count visible rows only.

AGGREGATE's third parameter: The data to aggregate

On to the next comma and now we need to select the range to count.  Now in this part we have two options.  Personally, I prefer to provide the range of the cells that will be hidden.  In truth though, you only really need to refer to a single cell in the range that will be collapsed.  Here’s what I went with:

=IF(AGGREGATE(3,5,A3:A6

Wrapping up the IF test

Perfect, and now we can just close the parenthesis and complete the test:

=IF(AGGREGATE(3,5,A3:A6)=0,

So, if the count of visible cells equals zero then… what do we want to do?

The IF test: If there are no visible rows...

This is the part that I think really makes this trick work.  I really like providing the arrow key to point to the + icon that shows up, and adding the additional wording as needed.  This allows my users to know not only that there is hidden data, but how to display it again.  So for me, that message might look like:

  • “<-- Show assumptions”
  • “<--Click to expand Revenue assumptions”

You get the idea.  For this example I’ve gone with the following:

=IF(AGGREGATE(3,5,A3:A6)=0,“<-- Show assumptions”,

The IF test: If there are visible rows...

And finally, we round it off with the messaging to provide if the count of visible rows is greater than zero (i.e. if the section is expanded).  Depending on what you want your model to do and how you want to display things for your end users, this could be something like:

  • “End of Assumptions”
  • “Total Revenue”
  • “Please insert new rows above this line”
  • “”

I think the first three are fairly self explanatory, but the last one is essentially two sets of double quotes.  Since everything between the quotes is returned to the cell as text, and there is nothing between the quotes, we get an blank cell.

The complete formula to use Aggregate to Count Visible Rows

Using that method, the finalized formula reads as follows:

=IF(AGGREGATE(3,5,A3:A6)=0,“<-- Show assumptions”,””)

Final Thoughts

My clients love this little trick. It’s fairly easy to set up, and is super useful for allowing people to hide/show the model sections that they want/need to review, without having them bogged down with all the info.

I also find it very useful when we’ve got multiple scenarios laid out on the worksheet. Say I need to look at… scenario 1 and 3 at the same time, I can compress 2 and just focus on the stuff I need to look at, avoiding scrolling up and down.

Can users see my raw data in Power BI?

One of the most important questions we have when publishing our data models and Power BI reports is Can users see my raw data?  The answer to this should virtually always be no unless you’ve explicitly stated you want this to be the case.  In this post we’re going to look at this problem and show you why this is a really serious question.

What kicked off this post?

At last week’s Power BI meetup in Vancouver, Peter Myers was demoing the feature to create a public facing web page (as I showed in this post.)  He quickly took his raw data, threw together a few visuals (without writing a single custom measure) and published the page to the web.  All in all it took less than 3 minutes for him to do this.

And naturally as he demoed to everyone that they could indeed get to the dashboard and play with it, the question of “Can users see my raw data” came up.  I confidently said no, and then it happened…. someone pointed out they could drill in to all the records that made up the data point.

It was a mic drop moment as that completely violated what we were expecting.  Part of the whole mantra of Power BI that we’ve been celebrating is that users only see what you want, and can’t get back to the raw data.

The “See Records” Feature in Power BI

Sure enough, this person was able to right click a chart and say “See Records”.  So what does that do?  It pulls up the full details of each transaction line that led to the data point:

image

Even though it doesn’t pull the records for the entire data set, it was still pretty horrifying.  With enough time, a reader could drill in and discover things about the underlying data that had never been intended to be revealed.

EDIT:  It's 2 days after the intial post date, and something has already changed here.  While See Records still shows the raw record set, it now only shows the columns that contribute to the chart, not the extra columns.  There could still be an issue, but it's not as bad as it used to be.

EDIT2: After a bit more diving in here, I discovered that it was me that changed something by hiding some of the model columns in Power BI desktop.  Hiding columns in the underlying table prevents them from appearing, but you still see all raw data points.

If you want to try this yourself, you can do so on the public version of the report I used for this post, which you can find right here.

The “See Data” Feature in Power BI

Seeing this, I immediately jumped over to the Hotel Stays report that I had published and checked my visuals.  Oddly, none of my visuals had the See Records feature.  They did however, have a feature called “See Data” which concerned me a bit at first.  But once you dig into it you’ll find it yields results like the following:

image

So basically it’s just a table view of the data that is already shown in my chart anyway.  It doesn’t expose anything new.  To be completely honest, it doesn’t add a ton of value as you don’t seem to be able to copy the table from the page or anything.  So the good news here is that no confidential data is being exposed.

Can “See Data” accidentally expose confidential data?

There is one case that I’ve found where the See Data function could actually expose confidential data, and that is when using map visuals.  On my Nights Away From Home report I used a map visual to plot the data points.  If you right click any of those data points and choose See Data, it gives you a list of all the data points.

SNAGHTML2ce4f25

Because I used a full street address to plot these on the map, I get a full list of detailed addresses. So if privacy is a big concern here I’d suggest you plot your data points using a postal code or city.  Something with less granularity. In my case I’m not totally concerned as these are all public hotels.. except for my friends locations where I had already adjusted the addresses to preserve their privacy by their request.

The Big Question: Can users see my raw data in Power BI?

So the simple answer would appear to be “Yes” they can.  But it’s actually a bit more complicated than this.  The Show Data feature is not – in my opinion – a data security risk.  The data is already shown in a visual, it’s just a different way of stating it.  So the real issue is around that Show Records option.

There are actually two big questions here…

  1. Can I disable the Show Records option?
  2. Why did my dashboard NOT have the Show Records option where Peter’s did?

As you can see, the second question actually answers the first.  It IS possible to disable the Show Records feature, but how?  As Peter and I were scratching our heads on this I decided that I needed to find out.

Controlling the ability to see my raw data in Power BI

As it turns out, the secret comes down to two things:

  • The type of visual you choose
  • The type of measure you create

I should also point out that the See Records and See Data features can be triggered from within Power BI Desktop.  This is fantastic as it means that you don’t need to publish to web to test your dashboard.  (Yay!)

The Data Set

To demo these features and issues, I’m going to use a subset of the data I had for my Nights Away From Home report:

image

To quickly explain this, I have three data columns in the table:  City, Country and StayCounter.  The first two are obviously text columns, and the last is a numeric column where each row contains the value of 1.

The last two items are DAX measures defined as follows:

  • CityCount = COUNTA(Stays[City])
  • ExplicitStayCounter = SUM(Stays[StayCounter])

Can users see my raw data in Power BI if I use a Card Visual?

To answer this, I created two card visuals.  The first was created using an Implicit measure.  I.e. I dragged the text based City field onto a card and let Power BI create an implicit “Count of City” measure:

image

The good news here is that there is no right click context menu for a Card.  (Give it a try!)

So using a Card visual means that your audience can never drill in to the underlying data.  I’m happy with that part.

Can users see my raw data in Power BI if I use a Bar Chart Visual?

Once again, I created two visuals here:

Field Red Visual (on left) Green Visual (on right)
Axis Country Country
Value City CityCount

In the case of the green visual, I have explicitly created my own measure by writing DAX.  In the case of the red visual, I have an implicit measure where Power BI has done the measure creation for me.  (Interestingly it reports the measure as Count of City which is not technically correct… it is actually a COUNTA of City, as Count can only count numeric values, where City is a text based field.)

Semantics aside, have a look at what you see for options:

SNAGHTML2ebf2ab

This was a bit shocking to me… so Power BI defaults to allowing you to drill into the underlying records… at least for implicitly generated measures that count text based entries.

Can users see my raw data in Power BI if I use an implicit SUM measure?

So the next question I had to explore is if this issue where users can see my raw data limited to only implicit measures based on text fields?  If the field was numeric and I use an implicitly created measure to count or sum it, will I have the same issue where the underlying records can be exposed?  Let’s take a look:

To avoid confusion here, I created to new visuals, but column charts this time.  Here’s how they were configured:

Field Red Visual (on left) Green Visual (on right)
Axis Country Country
Value StayCounter ExplicitStayCounter

The key difference here is that I just dragged the raw StayCounter column on to the red visual and let Power BI implicitly create the SUM measure for me.  In the case of the green visual, I wrote the DAX to say exactly what I wanted.  The two measures are mathematically equivalent which was important for an apples to apples comparison.  And here are the results:

SNAGHTML2f74c7e

(Yes, they look almost identical to the previous version, but if you check the titles – or play with the public version of the report - you’ll see that they indeed use different configurations.)

Observations and Thoughts

As you can see, the results are consistent with implicit versus explicit measures.  So if you want control of whether your audience can drill in to your underlying data records, you need to know this:

Measure Implicit – Power BI Explicit - DAX
Exposes “See Records” Yes No
Exposes “See Data” Yes Yes

This is a design issue

In my opinion, this is not good.  This basically means that the “quick and easy” way to create a report sets up to potentially expose data that should not be exposed.  And it’s not only applicable to reports that are shared publicly, it’s applicable to all reports distributed via Power BI Desktop, organizational level sharing and external sharing.  My feeling is that the Show Records feature should be disabled by default, and there should be a flag that you need to enable in order to enable this feature.

Having said this, the Power BI team has a problem.  I’d say that the vast majority of their audience do NOT want the See Records feature enabled by default, but some people rely on it.  This is one of the pain points of having something out there in the wilds, then realizing that something isn’t working the way you want.  To fix this, they’d need to released the a method to configured the visibility of the Show Records feature (probably to Power BI Desktop) then switch the default on Power BI.  Someone will no doubt complain of a loss of the feature, but as long as there is a way to fix it I’d say the risk of upsetting a customer in this manner outweighs the risk to Microsoft's entire customer base of accidentally exposing company confidential information.

My personal feeling is that I’d like to see both the See Records and See Data features configurable by the report author.  I’d like to see both off by default, with the ability to turn them on as needed.

In the mean time…

While current implementation is ultimately not what I think should happen, I’m actually happy it is consistent.  Why?

As it turns out, the reason I never saw this is that I’m in the practice of taking explicit control of my data and measures.  Since my earliest days of working with Power Pivot in Excel, I have never relied on the implicit measures, electing to always write my own using DAX.  This is true even for simple measures like SUM(Stays[StayCounter]).

Part of my reason for this is just habit, part is because I want to learn, part is because I don’t trust some of the implicit stuff that happens and don’t trust defaults.  In VBA we have a specific command to force variable declarations which is called “Option Explicit”.  I’ve always adopted that as not just a code word, but rather as a development standard.  In this case I’m fortunate that it saved my data.

If you don’t know DAX, you should learn it.  Not for this reason alone, but it is certainly another reason.