Reformatting my Data in excel for Tableau

Question

Example Data format My dataset arrives in this format on a CSV file:

Platform Group Sub Group Category 2022-11-01 2022-11-02 2022-11-03

With each date as a column header going forward.

Each row represents one piece of data and each complete daily set is under the dated column. For all data on Nov 1st, you refer to the 2022-11-01 column. There are hundreds of categories so each date has hundreds of data points.

My issue is this format doesn't work very well with Tableau.

When i connect this data source into tableau, it naturally reads each date as a separate category but my goal is to have a "date" field in which i can choose the platform, group/subgroup and category then create a trending line graph with the dates.

My goal is to restructure this data in excel, then re-connect it to Tableau. VBA code can be used if necessary but not sure how to approach.

Any help would be appreciated!

Attempted to transpose the data but gives me the same problem in reverse.

You can search here on SO for "unpivot" or "depivot" - this type of question has been asked before and there are several approaches posted as answers. Eg: https://stackoverflow.com/questions/36365839/transpose-multiple-columns-to-multiple-rows-with-vba/36366394#36366394 — Tim Williams, Jul 14 '23 at 17:14
Thanks! Didn't know the keyword "unpivot" was what i was looking for. Was able to figure something out. Thank you! — MALBC, Jul 14 '23 at 19:32
You can also use `Power Query` other than `VBA` here is an example : **[Unpivot](https://stackoverflow.com/questions/76688957/how-to-turn-columns-into-rows-depending-on-cell-conditions)** — Mayukh Bhattacharya, Jul 14 '23 at 20:11

score 1 · Answer 1 · answered Aug 16 '23 at 13:22

Tableau has built in support for pivoting columns to rows to simplify analysis. Described in the on line help here.

https://help.tableau.com/current/pro/desktop/en-us/pivot.htm

For more complex reshaping tasks, you can use Tableau Prep for pivoting and other data curation tasks.

score 0 · Answer 2 · answered Jul 15 '23 at 00:03

You can easily unpivot data with VBA code. Of course, PowerQuery unpivot is also a good approach.

Sub UnpivotData()
    Const COL_CNT = 4  'quantity of fixed columns
    Const S_FLD_NAME = "Date" 'new field name
    Dim ws As Worksheet
    Dim inLastRow As Long, inLastCol As Long
    Dim outLastRow As Long, outCol As Long
    Dim i As Long, col As Long
    Set ws = ActiveSheet
    inLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
    inLastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' header line
    outLastRow = inLastRow + 2
    ws.Cells(outLastRow, 1).Resize(1, COL_CNT).Value = ws.Cells(1, 1).Resize(1, COL_CNT).Value
    ws.Cells(outLastRow, COL_CNT + 1).Value = S_FLD_NAME
    ws.Cells(outLastRow, COL_CNT + 2).Value = "Value"
    outLastRow = outLastRow + 1
    ' unpivot data
    For i = 2 To inLastRow
        For col = 5 To inLastCol
            ws.Cells(outLastRow, 1).Resize(1, COL_CNT).Value = ws.Cells(i, 1).Resize(1, COL_CNT).Value
            ws.Cells(outLastRow, COL_CNT + 1) = ws.Cells(1, col)
            ws.Cells(outLastRow, COL_CNT + 2) = ws.Cells(i, col)
            outLastRow = outLastRow + 1
        Next col
    Next i
End Sub

Reformatting my Data in excel for Tableau

2 Answers2