You want to do some visualisation magic in Tableau, but your spreadsheet looks like this:
All those green columns are dependent variables: independent observations about one location defined by the white columns.
Tableau would be so much happier if your spreadsheet looked like this:
This is called “normalizing” the “cross-tab” format, or converting from “wide format” to “long format”, or “UNPIVOT“. Tableau provides an Excel plugin for reshaping data. Unfortunately, if you don’t use Excel, you’re stuck. It’s kind of weird.
Anyway, I’ve made a Google Sheets script “Normalize cross-tab” that will do it for you.
As the instructions say, to use it, you:
- Reorganise your data so that all the independent variable columns are to the right of all the dependent ones; then
- Place the cursor somewhere in the first (leftmost) independent variable column.
It then creates a new sheet, “NormalizedResult”, and puts the result there.
How to use
It’s surprisingly clumsy to share Google Scripts, at least until the new “Add-ons” feature is mature. Here’s the best I can do for you:
1. Copy the script to the clipboard
Go to https://raw.githubusercontent.com/stevage/normalize-crosstab/master/normalizeCrossTab.gs, select all the text, and copy to the clipboard.
2. Upload your spreadsheet to Google Sheets
Upload your Excel spreadsheet into Google Sheets, if it’s not there already.
3. Tools > Script Editor…
4. Click “Spreadsheet”
In the window labelled “Code.gs”, select all the text and paste over it the script from the clipboard.
You need to give this script “project” a name. It doesn’t matter.
7. Select the “start” function.
8. Click Run
Click Continue and accept the authorisation request.
9. Follow the instructions of the script
Now, switch windows to your Google Sheet, and you’ll see the sidebar.
10. Download your normalised spreadsheet
On the NormalizedResult page, choose File > Download as…
If you want to convert several spreadsheets, you can save yourself pain by loading them all into the same workbook. Just remember that the script will always save its output to NormalizedOutput.
This works perfectly, thank you!
Tried this but my normalized sheet was just blank, save for the “field” and “result” headers. Any idea what I did wrong?
Great script. I noticed that if the dependent variables are of DATE type, the script doesn’t work, but by switching dates to TEXT, it works. Thanks!
This saved me so much time with my Tableau group project. Thanks so much!
is there a fix for the DATE Type issue? I can convert it to text but then it is text and not a date which creates further problems.
Pingback: Eine Tabelle in Google Tabellen entpivotieren – Jacob Fricke