What is the easiest way to visualise datasets structured like a matrix?

@Brenton, did creed's reply help answer your question? 

No problem Brenton! With the data in a matrix format we will be able to create a bar chart with the help of a few simple steps in a MagicETL dataflow. "Unpivot" simply means to turn the columns into rows. You can also call this operation "transpose" or "collapse". The goal is to take a datset like this (pivoted with values spanning many columns):

...and transform it into something like this (unpivoted with values for those columns now spanning rows):

Having the data in this format allows us to build standard cards like this:

Here are the steps using MagicETL.  We will be adding an Input Dataset, Collapse Columns transformation, Set Column Type transformation (optional), and finally an Output Dataset.

Step 1: Create a Magic ETL dataflow and bring in the source dataset.

Step 2: Add the "Collapse Columns" transformation in the "Edit Columns" section. In the transformation settings it asks you for the column names for the category and value columns that will be transposed into rows. In my example I am naming this new column "Date" to hold the dates that were previously populated in each of the columns, and "Amount" for the amounts that were in the intersection of each location and month. In the next section of this setting you will enter the list of columns that you wish to transpose.

TIP - you do not need to manually enter each of the columns. You can select the "ADD ALL COLUMNS" option and it will automatically bring in each column and give it a new label. It will also bring in the location column so you will want to remove that since we do not need to transpose that column. It will simply be copied down for each new row that we create.

Step 2a: You may need to tell MagicETL that the new columns are a specific data type (i.e. Decimal for a numeric value, or Date for a date value). This can be done using the "Set Column Type" transformation in the "Edit Columns" section. You can check what the data type is currently set to by clicking on the "Add Column" option on the Set Column Type transformation settings.

In this example it thought that "Date" was text (should be a date) and "Amount" was a whole number (should be decimal). If this is not set correctly then the card won't display the information correctly, so it is important to verify that the correct data types are set.

Step 3: Once you have added the transforms then you can select an Output Dataset and run the dataflow. It will produce the unpivoted dataset so you can then build a card.

Those are the steps, I know it's a lot of information (and screenshot overload!) but once you have the general idea it isn't too bad. The only caveat is if you add new columns to your source dataset then you will also need to update the Collapse Columns transformation. It will not automatically detect new columns to transpose. If a dynamic/automatic solution is needed then MySQL will likely be needed. 

I hope this answers your question, but if not- please let me know and I'll clarify where needed. 

Thank you,

Creed