Previous Articles

White Paper: The free-role data analyst (Ned Stratton: 4^th September 2023)

Do data analysts need to read books? (Ned Stratton: 10^th May 2023)

No code data tools: the complexity placebo (Ned Stratton: 17^th March 2023)

The 2023 data job market with Jeremy Wyatt (Ned Stratton: 24^th January 2023)

Making up the Numbers - When Data Analysts Go Rogue (Ned Stratton: 2^nd December 2022)

Data in Politics Part 2 - Votesource (Ned Stratton: 12^th September 2022)

Data in Politics Part 1 - MERLIN (Ned Stratton: 2^nd September 2022)

Interview: Adrian Mitchell - Founder, Brijj.io (Ned Stratton: 28^th June 2022)

The Joy of Clunky Data Analogies (Ned Stratton: 14^th April 2022)

Event Review - SQLBits 2022, London (Ned Stratton: 17^th March 2022)

Interview: Susan Walsh - The Classification Guru (Ned Stratton: 21^st February 2022)

Upskilling as a data analyst - acquiring knowledge deep, broad and current (Ned Stratton: 31^st January 2022)

Beyond SIC codes – web scraping and text mining at the heart of modern industry classification: An interview with Agent and Field's Matt Childs (Ned Stratton: 8^th December 2021)

Debate: Should Data Analytics teams sit within Sales/marketing or IT? (Ned Stratton: 26^th October 2021)

Event Review: Big Data LDN 2021 (Ned Stratton: 27^th September 2021)

The Swiss Army Knife of Data - IT tricks for data analysts (Ned Stratton: 9^th September 2021)

UK Google Trends - Politics, Porn and Pandemic (Ned Stratton: 15^th October 2020)

How the UK broadcast media have misreported the data on COVID-19 (Ned Stratton: 7^th October 2020)

The Power BI End Game: Part 3 – Cornering the BI market (Ned Stratton: 21^st September 2020)

The Power BI End Game: Part 2 – Beyond SSAS/SSIS/SSRS (Ned Stratton: 28^th August 2020)

The Power BI End Game: Part 1 – From Data Analyst to Insight Explorer (Ned Stratton: 14^th August 2020)

Excel VBA in the modern business - the case for and against (Ned Stratton: 13^th July 2020)

An epic fail with Python Text Analysis (Ned Stratton: 20^th June 2020)

Track and Trace and The Political Spectrum of Data - Liberators vs Protectors (Ned Stratton: 12^th June 2020)

Defining the role of a Data Analyst (Slawomir Laskowski: 31^st May 2020)

The 7 Most Common Mistakes Made in Data Analysis (Slawomir Laskowski: 17^th May 2020)

COVID-19 Mortality Rates - refining media claims with basic statistics (Ned Stratton: 10^th May 2020)

Ned Stratton: 22nd November 2023

I wrote earlier this year in March about no code and how it was a placebo rather than a medicine for complexity and knowledge-transfer angst in data solutions.

Well, I'm returning to the subject with a vengeance for what will probably be the last UCOVI Blog post of 2023. This is because Q4 2023 has got me using Alteryx. Has this exposure changed my mind about no code tools?

In a clunky flashback to my article from March, I made an even clunkier Formula One analogy about Alteryx's no-code-data market leadership and said that they were the Max Verstappen of the No Code F1 paddock. It turned out not to be too wide of the mark, as Alteryx list F1 3rd-place hopefuls McLaren among their clients and have them front and centre of their marketing.

I want you to watch their Youtube promo from earlier this year and take heed.

Observe the sass 20 seconds in when power-striding, tracksuit-clad, young, smart, attractive Antipodean McLaren girl says **no code needed**, causing desk-sitting boomer Steve to sit up and take note, mind totally blown. He even has floppy disks, the silly old sausage. The implication is that Alteryx is the driver, the engine, and the team principal of data-driven performance and insight, and data nerds with their Python, SQL and Fortran are now as obsolete as the incompetent, slow mechanics doing 15-second pitstops from the 90s days of Damon Hill.

If these guys have ever spoken to an actual data engineer or analyst before, it evidently happened in the same room the McLaren F1 engines were being tested, or when said data engineer was intensely debugging code and thus making no verbal sense at all.

I'd like to put Alteryx's "no code needed" claim to the test.

I was using it to blend together a range datasets in various flat-file formats, including Excel spreadsheets. Nothing out of the ordinary here, but we're about to hit a snag. The data I needed from Excel was in data tables (Ctrl + T) across several worksheets. The worksheets could contain more than one table, as well as additional standalone formula cells outside of the table made by the person maintaining and using it.

Point being, the fact of it being in an Excel table is what identified it as the data needed in Alteryx, which unfortunately cannot recognise or support official Excel tables. It can support specific cell-address A1:F237 ranges or name-manager named ranges but has no concept of Excel data tables, which play best with Power BI, are what modern Excel Power Query transformations and routines are read into, and have any additional columns added to them automatically reflected in pivot table refreshes. Hardly obscure functionality for use in edge cases.

Addressing this doesn't seem to be on their dev roadmap, and it has been asked about twice on their Community Support page, here and here. Both times, the person responding from Alteryx seems to adopt the tactic of pretending that an Excel data table and a named range are the same thing and giving them named range support, then continuing to play dumb for a bit, before finally saying "Well if the range updates Alteryx won't pick it up, too bad."

So to the million dollar question: how do you solve a problem like Excel data tables in a state of the art data blending tool that obviates the need for code but doesn't support Excel data tables?

4 options:

• Read the whole sheet in and locate the first null column in the header row, and the last row where there's a data value. Flimsy and quite frankly ridiculous.
  
• Run some VBA on the Excel file to add a named range alias for each table, so that there are named ranges for Alteryx to read that mimic the data tables. Better, but an automation stumbling block.
  
• Ask the person who maintains the Excel file to stop using Excel data tables and save the data you need in specifically named sheets with no other items, because the no code blending tool your company has forked out £4k per user per year for needs them to. Sub-optimal.
  

Option number 4, the one I went with, was to use some code. The Alteryx Run Command tool allows you to run PowerShell - which comes as standard on a Windows and is as easy as Python to script with - as long as your PowerShell script saves to a csv file for the tool to read into the workflow as output.

So in 130 lines of my best no-code-tool-mandated code, I wrote a routine to save an Excel file as a ZIP archive, unzip it, loop through the XML files that live under the hood of a .xlsx and which conspire to tell you the sheets and cell ranges of your data tables, and then write this out to a csv and back into the Alteryx workflow.

If someone out there somewhere has had the same trouble with Excel data tables as I've had and appreciates my solution, I'll be a happy man and encourage them to get in touch. But I'll be even more happy if someone who would otherwise go down the no code data route after a steak and a sales pitch from an Alteryx account manager reads this and decides to put their trust in the data-team Python whiz instead.