CODEWITHSUNDEEP
excelvbadatabasetabulartableau-desktop
MALBC
MALBC

Reputation: 21

Reformatting my Data in excel for Tableau

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.

Upvotes: 2

Views: 141

Answers (2)

Alex Blakemore
Alex Blakemore

Reputation: 11921

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.

Upvotes: 1

taller
taller

Reputation: 18923

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

enter image description here

Upvotes: 0

Related Questions