A couple of weeks ago I wrote about a Tableau Prep approach to transposing data from a text file that had a field per line, with another line separating records. At the time I noted that the approach wasn’t robust enough to handle optional fields, and that it would be annoying to need a join per field in cases where you had a large number of fields. In this follow up post I look at an alternative that doesn’t have those drawbacks.

The basic approach is to use a pivot of rows to columns, along with a record number to group fields from the same record. I also introduce a mapping table to allow for less hardcoding of field name and the record separator.

Here is the example source data I have used:

You’ll notice that Hobby and Food are optional fields that don’t appear on every record.

I also have an Excel sheet the defines the record structure:

Here you will see that I map a Field Label (which appears in the source date) to a Field Name that I want in the output. There is also a field called “(end)” which defines the record separator – in this case a blank line.

The Tableau Prep flow is as follows:

This flow:

• Loads the source and structure files
• Prepares them to allow them to be joined
• Joins them together so that we have field names and know the “end of record” lines
• Calculates a record number by looking for the “end of record” lines
• Pivots the data from rows to columns
• And outputs the result

When loading the source data I generate column names (F1, F2) and switch on the extra source row number field (which will be required for the record number calc later):

The “prep to join” steps replace NULLs with empty strings (for the record separator lines) and, for the source data, renames F1 and F2 to Field Label and Value.

The join step is now fairly straightforward, just joining the source data to the structure info on Field Label:

It results in the data like this:

For the pivot step we need something that groups fields for a record together. The “calc record num” step does that, as well as a little tidy up to remove now unnecessary fields and the record separator lines.

The calc for Record Number is using a running sum to work through the source lines in order (ORDERBY [Source Row Number] ASC), generating a number that increments every time we hit an “end of record” line:

{ORDERBY 
 [Source Row Number] ASC:
RUNNING_SUM(
 IF [Field Name]='(end)' THEN 1 ELSE 0 END
)}

A couple of notes re this calc. It actually makes the “end of record” line part of the next record, but as those lines will be filtered out anyway that doesn’t matter. I also have a follow up calc that adds 1 – it isn’t really necessary but Record Number “0″ looks a bit strange! This is the part that avoids the problem in my previous approach where optional fields weren’t catered for. Previously I was using whole number division to generate a record number from the source row number, and that relied on a set number of fields. The running sum option just looks for the record separators.

Next up, a basic pivot translates the rows to columns:

The pivot requires an aggregation for the values, but as we only have one value per row we can pick either MIN or MAX here. This pivot avoids the join per field that I had in the previous solution.

The output step then outputs the results, in this case:

NB: bold/line added after output.

The new approach successfully handles optional fields, doesn’t require a join per field, and has the added benefit of a mapping file to define structure. One downside is that the field/column order is reversed, but as the resulting CSV is for analysis in Tableau (say) that doesn’t really matter. In my first run I didn’t include the “Food” field; adding that in for a second run. So the solution is reasonably plug and play! You can use the same flow with your own source data and structure definition, you just have to right click the pivot step and “refresh” for it to pick up new field names.

A packaged flow for use in Tableau Prep is available on the Tableau Forums.

This quarter I set myself the goal to learn more about Tableau Prep, and a key part of that has been participating in the weekly #PreppinData challenges. Something I’ve noticed, and have been super intrigued about, is that some participants have been posting one-step solutions. That wasn’t surprising during beginner month, but now I’m seeing one-step flows covering reasonably complex multi-step data transformations. Cool!

This week I took a deeper dive into one of those one-step solutions to learn, and share, how they’re being done.

Two quick things first: They say a magician never reveals their secrets, so my apologies in advance to those Tableau Prep magicians who’d rather not see the “magic” shared. Also a hat tip to Hiroaki Morita, who’s week 7 solution I picked as the example to dive into. If I don’t do the techniques justice that’s on me, not Hiroaki!

Right, what do we mean by a “single step” solution? Basically, a single step between the input and output, like so:

For comparison here is my solution to week 7, which has two inputs and then four steps before the output:

The first thing you may notice is that there is only one source: a UNION to pull the two required data sets together. That might seem strange to you as the two tables of data are quite different. I’m going to add a quick “clean” step off the union to take a look at what it’s producing:

What we’re seeing here, boxed in red, is that the two tables are indeed unioned together, and so we get two chunks of data. The first chunk has the columns and rows from the first table of data (the couples and when their relationship started), and the second chunk has the columns and rows from the second table (the gift relevant to each year of a relationship – this challenge was about finding the right gift for each couple based on the length of their relationship).

This union approach seems to be a hallmark of single step solutions; get all of the data into one place/table so that we can operate over it in a single step. It does leave us with a challenge though as it’s not structured or related in the sorts of ways we’re used to, so let’s see how that is dealt with in the single clean step.

There are four sub-steps (or changes) in that clean step:

The first change is to calculate a consistent field across the two sets of data – in this case a “number of valentines days as a couple”. Where Year is NULL (no data) we’ll use the relationship start date [A] Where Year is not NULL we’ll remove the st, nd, rd, th letters from that Year field to just leave a number [B]. In a multi-step solution this would be the consistent field to join the two data sets together.

Because we have all the data in one data set we don’t need to join, instead we need to “look up” the gift from chunk B and plug it into chunk A (where it is missing). And we want to do that based on the consistent field calculated above. So in the screenshot above we can see that we have “number of valentines days as a couple” = 4 on the top row (for “The Loves”) but no gift (it is null). But we also have a row in chunk B with “number of days…” = 4 where we do have the gift (“Fruit/Flowers”).

The next change handles the look up. It uses a FIXED level of detail expression to say “get me the maximum gift from across the whole data set, for this number of valentines days”. Aggregations like MAX will ignore NULLs so we in essence look up the gift from chunk B:

This is potentially another hallmark of one-step solutions then: lookup the value you need from further down the combined data set, rather than using joins.

The fourth change is to filter down to just chunk A, chunk B was only there for the look up after all:

After that the solution simply removes the unecesary columns to be ready to produce the output. Clever, eh!

For me the key points of this one-step solution were:

1. Get all of the data into one place/table so that we can operate over it in a single step.
2. Lookup the value you need from further down the combined data set, rather than using joins.
3. Filter out the data we only pulled in for the lookup.

I hope that you found this as intriguing as I did. And if you’re interested to see more one-step magic keep an eye on the #prep1stepclub X/Twitter hash tag!

UPDATE: Following a really good discussion with another member of the data community I thought I’d add a few notes about why and when you might use a one-step solution. Our conclusion was that one of the strengths of Tableau Prep is it’s clear, easy to understand and maintain, visual layout. Maintainability is a really important consideration, so you may never* use a one-step solution in production, favouring clarity and maintainability instead. However for your own professional development one-step solutions present a useful challenge. They introduce a constraint that forces you to think about problems differently, and in all likelihood use product features that you wouldn’t normally use. That gives you good practice. And afterall, why do we climb hills and mountains that we could otherwise go around?

* Although I say “never” I should point out that I haven’t performance tested common one-step solution patterns against their more natural counterparts. Consequently there may be some benefits (or indeed further drawbacks) that I’m not yet aware of. 

I recently answered a question on the Tableau Community Forums about transposing data from a text file that had a field per line, with a line of dashes separating records. I’m not sure what the formal name for this format is, but there are similarities with RecFiles.

Here is an example:

I don’t know of a way to use data formated like that directly in Tableau Desktop. But we can use Tableau Prep to transform it into a more natural row per record format!

In this post I’ll cover how I suggested doing that for the forum question. And I plan to follow up with some more generic and robust options in a future post.

First lets take a look at the overall flow given the data above:

What we have here is:

• An input step (on the left) to load in the file
• A “clean” step to add a record number to each line
• Three steps to separate the lines for each field
• Join steps (Name+Age and Name+Age+Eyes) to join the data set for each field back together to give a traditional record structure
• An output step to write out as CSV

Diving into each of these components:

The input step defines a split on TAB, headers (F1, F2, etc.), and enables the built in source row number that Prep can add. This row number will be important for identifying a record number next.

The next step adds a record number and removes the dashes which act as a record divider:

Record number is calculated using:

DIV([Source Row Number]-1,4)+1

This is basically just a whole number division (using DIV) of the row number by the number of rows per record, including the divider (4 in this case). Then we filter out the rows with the dashes to get rid of the record dividers. Note that I’ve also neatened up the field names in column F1 above to remove the colon.

Caveat: Because the record number is based on an expected number of fields, this approach won’t be robust enough to handle optional fields that do not appear on some records. This is one reason I’d like to come back and do another post on the topic!

Next we have a clean up step per field to grab just that field and it’s row number, including renaming the column header (F2) to the field name. Here is the step for “Name”:

This is repeated per field (annoyingly if you have a large number of fields!) but starts to get us closer to what looks like a row per record.

At this point though each step gives us a record with just one of the fields, and its record number. So next we need to join these up, two at a time, to bring the fields per record back together:

One more of these joins gives us a final output like this…

… meaning that we’ve successfully transformed a data set where each field is on it’s own line, into a more traditional row per record / CSV format, which is much more suited to analysis in a tool like Tableau Desktop.

Watch this space for part 2 where I dig into alternative and more robust approaches – e.g. to handle optional fields!

UPDATE: part 2 is now available.