How To Transpose Data In Alteryx

How to Restructure Information in Alteryx: Files With Two Column Headers

• Aidan Bramel
• Jan 1, 2022

Equally analysts and developers, we don't always have control over the information we receive and how information technology is formatted. We still need to practice our all-time to deliver what we tin with what we get. Recently I was given the task of creating a Tableau dashboard off of a file that contained cavalcade headers split out into the kickoff two rows. The first row of cavalcade headers contained a date field, and the second row of headers contained production hierarchy and measure name information. This is definitely non how reporting tools, such every bit Tableau or PowerBI would expect to run across the information, so it'south going to accept some Alteryx work to get where it needs to be.

Step 1: Input the 2 Header File

For better or for worse, we work with what nosotros've got. Permit'south input it into Alteryx and restructure our files so they tin work their best in Tableau.

For the sake of this sit-in, I've created a sample file out of superstore data that emulates the structure of common files I've seen that incorporate two rows of headers. It looks a little something like this:

• The first row of headers: timeframe of the metric results: January 2020, February 2020, etc.
• The 2nd row of headers: proper noun of the dimension in the production hierarchy or the proper noun of a mensurate within the timeframe from the first row.

The first thing I'm going to do is input this file into my workflow. I tin can do this past selecting it in the Input Tool's file connector, or directly drag and drop the file from my file explorer.

As I pull in this file, my first row containing columns will become our initial headers. Our second row of headers will become the first row of inputted data. Considering I have repetitive headers for the timeframes, Alteryx will also append numbers to each cavalcade to ensure no repeats. This is fine, and nosotros'll fix this in the hereafter.

As I mentioned, our first row of inputted "information" is actually our 2nd row of column headers. Let'due south isolate those headers from our actual information into two separate streams of data. I'g going to use ii sample tools: one that selects the first row, and one that skips the first row.

Our "First 1" Sample tool will now output our sample headers. We're going to proceed onward with that stream as we movement forward, and volition somewhen revisit the "Skip 1st 1" stream, which contains all of our data.

Step 2: Use the Transpose Information to Pin Columns

Now we will pull in the Transpose tool. No configuration is required on this tool in this case, every bit we want to pivot all of the columns. I'm going to use this to plough both the column names and first row of data into rows that comprise two fields:

• [Name]: the name of the original column, as information technology appears in our original data stream. This volition be of import to keep with us for futurity steps.
• [Value]: The showtime row of information we isolated.

Stride three: Concatenate The Two Header Fields

Adjacent, we want to concatenate our header fields to get one consolidated name for our columns moving forward. I'thousand going to apply the Formula tool to edit our [Value] field to suspend our date columns to the front, with a "_" delimiter in the middle. Nosotros are too going to get rid of the appended "_2" that ended upwardly in our repeated columns with a LEFT function.

In the case of the Category and Sub-Category field, I don't want the F1, F2, etc. to come up through, so I'll use an IF clause to exclude those fields. The formula ends up looking like the below:

Step 4: Use Dynamic Rename to Input New Field Names

I now have ii fields that contain the original field proper noun and the new field name. We're perfectly set to use ane of my favorite tools: Dynamic Rename!

We are going to utilise both of our data streams to input this tool. We are going to connect our information stream without the headers to the 50 input anchor. The R input anchor is optional, but in this instance, we are going to utilise the R ballast to input our new field names.

This will enable us to configure the tool in rename mode "Take Field Names from Right Input Rows". We'll have the sometime fields from the [Name] field, new field names as our concatenated [Value] field. This will result in our original data stream existence renamed according to the new names we created. Boom – crawly!

Step 5: Use Text-to-Columns Tool to Separate Information (Optional)

Depending on what your ii rows of headers indicate, y'all can likely cease at this step and continue on with your analysis. However, in my case, I'll take this a step further by besides turning our date portion of some of the headers into rows. It is important to exist able to piece and die by appointment when building a information product and getting the dates into the rows is the best way to exercise that.

I use the same method with the Transpose tool to turn the columns into rows, this fourth dimension group past the central fields of [Category] and [Sub-Category]. This enables me to use the Text-to-Columns tool to split the date from the measure names on the "_" delimiter we added in our formula tool.

Step 6: Use Cross-Tab to Group Data Fields (Optional)

After removing the original field with our Select tool and renaming our fields to be more articulate ([Date] and [Measure out]), we tin then Cross-Tab the remaining fields, once again grouping on [Category] and [Sub-Category] but this time also selecting [Date] to group past. We will use [Measure out] as the column headers and [Value] as the new values. Your method for aggregation should not matter as nosotros will not need to aggregate any further.

In the finish, you should be left with a data source that has Columns for each dimension and mensurate, and dates for each row. This should be perfectly optimized for Tableau or any other reporting tool.

Exercise y'all accept more questions about Alteryx? Talk to our expert consultants today and have all your questions answered!

Table of Contents

More to explore

Source: https://www.phdata.io/blog/how-to-restructure-data-in-alteryx-files-with-two-column-headers/

Share this post