Skip To Content

6 Excel Data Challenges Rapid Insight Helped Me Solve

Reading time: 3 minutes

By Mary Anna Sanguinet and Jon Macmillan, Data Analyst and Senior Data Analyst

As a long time user of Excel, what I love about Construct is its ability to make some of the more tedious Excel processes so much easier to accomplish. Here are six quick tips to help solve your Excel data troubles.

1Make Merging Data Easier

Have you ever needed to join together data from two or more different but related spreadsheets?  It can be frustrating, especially if you need to do it often.  In Construct, this process becomes so simple that I have forgotten the pains that come with data prep before Construct was in the picture.

In Construct’s drag and drop interface, this process is simple:

  1. Drag and drop your data sources, a Merge node and an Output node into the workspace.
  2. Configure the join type.
  3. Select the fields you would like to display after the merge.

This took about one minute to set up and, the best part is, it’s repeatable.

2. Simplify Stacking Datasets

When you need to stack similar data from different spreadsheets to create one combined dataset, how many steps does that take in Excel?  What if the column name and data types differ between the different spreadsheets?  Do the same columns exist in each spreadsheet?

In Construct’s Append node interface, stacking data is a breeze and, with a couple of clicks, you can do all of the following, on the fly, without ever having to open Excel:

  1. Rename column headers.
  2. Change data types.
  3. Add additional columns to remind you where that row of data came from.
  4. View if data is present in each data source for each row.

3. Filter Data Efficiently

Sure, filtering data is not the most complex process in data prep, however, what if the filtering is done at the beginning of your process… and you did it wrong?  How easy or difficult is it to go back to and correct it in Excel?

In the Construct job here, we setup all of the data blending nodes and then ran it.  Without having to look at the result file, we can easily see that there are 143 records.  This is quite a bit more than expected.  After a quick check, we missed filtering out some data at the beginning of the job.

Not to worry, with Construct’s process job, we can go back, double click on the Filter node and add in the second filtering criteria.  All that’s left to do is to re-run the job to get the expected number of records.

4. Effectively Clean Up Data

When trying to clean up data in Excel using the Find and Replace function, it is not too difficult if there are just a couple of values that need to be replaced.  It can get cumbersome when there are many values to replace in multiple columns.

In Construct’s Cleanse node, cleaning up your data is quick and easy.  In the interface, once a variable is selected the user can click on the Get Values button and a list of unique values in that column is displayed for easy selection.  All of the replacement operations are in one location and can be reordered, if needed with a click of a button.

If there is any question as to what happened to a value, this window can be reopened and the operations reviewed at any time.  This node can be copied and reused other places where the same data exists, saving you time.

5. Streamline Periodic Reporting with Parameters

How are you currently accomplishing reporting that needs to be done weekly, monthly or annually? Do you have to reproduce these same processes manually to get results?

All jobs in Construct, once created, can be run over and over again.  Using the Parameter functionality in conjunction with jobs allows you to update a reporting month, a data range or another variable very quickly in just a couple of fields.  These parameters link to filters, formulas, and even output naming conventions, so by changing one field your job is ready to run for the new parameter.

In the job here, the blue highlighted nodes pull values from Parameters, so every month when this job is needed, simply change the State and the Date and run the job.

6. Make Tableau TDE Extracts Effortless

How are you currently moving your data from Excel to Tableau?

Whether you use Tableau Desktop, Online or Server, Construct has the Tableau output node for that.  Once you have done all of your data preparation for Tableau, simply use one of the built-in Tableau output nodes.

Depending on your preference, Construct will output an extract for use with Tableau Desktop or it can push the dataset directly to Tableau Server or Online.  Simply complete the parameter fields within the node and your data is ready for Tableau.

No matter your reporting or visualization needs, Construct has the tools to help you get the job done faster. Explore all of Construct’s nodes or visit our Construct page to learn more and download your free trial!

Stay up to date
Subscribe to our blog

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments