Possible Complex Transpose?

[NJudson]

Hello all. I've been struggling with re-formatting spreadsheet data I have to make it easier to bring into power bi for analysis and I was wondering if anyone might have any suggestions on how I can perform this action efficiently. I do currently have a working vba module that will re-format the data into a more useful format, but I'm not very good at it and it's clunky and takes nearly 30 seconds to run. There can be a lot of row data though so maybe that is why runs so long. I have as much as 170,000 rows when currently working with this. I may have 2-3 times that at other times though.

Here is an example of my original data on Input data sheet
Sector Date KPI
A1 12/1/2017 99.5
A1 12/2/2017 98
A1 12/3/2017 99
A1 12/4/2017 97
A2 12/2/2017 98
A2 12/3/2017 96.5
A2 12/4/1027 99.5
C1 12/1/2017 99
C1 12/2/2017 99
C1 12/3/2017 100
C1 12/4/2017 99.5
C1 12/5/2017 96
C1 12/6/2017 98.5
C1 12/7/2017 97



Here is what I want it to look like on an Output data sheet: ****Sorry for the data view, I am having trouble figuring out how to post the info in a "spreadsheet" view

12/1/2017 12/2/2017 12/3/2017 12/4/2017 12/5/2017 12/6/2017 12/7/2017
A1 99.5 98 99 97
A2 98 96.5 99.5
C1 99 99 100 99.5 96 98.5 97


By getting it into this new format I can have unique rows that I can set relationships on in power bi. The code I'm using so far to get this to work is:


Sub PopulateOutputDataSheet()

Dim i As Single
Dim j As Single
Dim m As Integer
Dim n As Integer
Dim lastrow As Long

Application.ScreenUpdating = False

Sheets("Output_Data").Select 'Clear Output_Data sheet
Cells.Select
Selection.ClearContents
Sheets("Summary").Select 'Clear Summary sheet
Cells(2, 1).Select
Selection.End(xlDown).Select
Selection.ClearContents

Sheets("Input_Data").Select
Cells(2, 1).Select
Selection.End(xlDown).Select
lastrow = (ActiveCell.Row) 'Find the number of rows the last row is on

i = 3
StartDate = Cells(i, 2)
EndDate = Cells(i, 2)
Do Until Cells(i, 2) = "" 'Find the start Date and end Date of the value period
If Cells(i, 2) < StartDate Then
StartDate = Cells(i, 2)
End If
If Cells(i, 2) > EndDate Then
EndDate = Cells(i, 2)
End If
i = i + 1
Loop

i = 2
postDate = StartDate
Do Until postDate = EndDate + 1 'Enters Date header row to Output sheet for given KPI period
Sheets("Output_data").Cells(1, i) = postDate
postDate = postDate + 1
i = i + 1
Loop


i = 2
j = 2
m = 2
n = 2
Do Until Cells(i, 1) = "" 'Output Sector and KPI to the corresponding date on Output_Data sheet
mySector = Cells(i, 1)
mydate = Cells(i, 2)
myKPI = Cells(i, 3)

Sheets("Output_Data").Cells(m, 1) = mySector
Sheets("Summary").Cells(m, 1) = mySector
For findDate = StartDate To EndDate
If mydate = findDate Then
Sheets("Output_Data").Cells(m, n) = myKPI
Exit For
Else
n = n + 1
End If
Next findDate
n = 2
If Cells(i + 1, 1) <> mySector Then
m = m + 1
End If
i = i + 1

Loop

Application.ScreenUpdating = True

End Sub




I was trying to figure out a way to implement a complex form of transpose to get this to run faster, but I'm not having any success in finding a way to do this. I'm checking with you folks to see if there is a better way to do this than what I'm already doing.

Thank you for your time. Cheers!

 

[veryamusing]

You can transform the data using Power Query. Given a table or range, these steps worked for me:
1. Pivot "Sector" Column
2. Transposed Table
3. Promoted Headers
Then, you can load to the Power BI Data Catalog.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sector", type text}, {"Date", type date}, {"KPI", type number}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Sector]), "Sector", "KPI"),
    #"Transposed Table" = Table.Transpose(#"Pivoted Column"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

You can also similarly transform using a PivotTable:
1. Columns = "Date"
2. Rows = "Sector"
3. Values = "KPI"

 

[NJudson]

Hi veryamusing,

Thank you very much for the advice. I had some trouble figuring out the code aspect you provided, but I did get this to work using the PivotTable. Very simple solution and works efficiently. Thank you!