How To Really Publish Excel Data To Power BI … Part Deux

A couple of weeks ago I published a length post detailing the techniques I have developed to successfully export Excel data to Power BI.  I have had some interesting responses and I have since evolved my thinking on this, and my “Best Practices” for using Excel with Power BI Desktop.

Last week I did a presentation on this topic to the Modeloff GTC in Toronto ( http://www.gtc.modeloff.com/toronto/ ) and in that presentation I included the use of a new bit of code that anyone can use to prepare their existing (or new) Excel files for exporting data to OneDrive for Business to use in Power BI Desktop.

This new code replaces the code I included in the last post (sorry about that but progress is progress 🙂 ).  It includes most of that code but rounds out the process including the creation of the necessary Worksheet for prepping for export and running the code. You can replace the previous code with this easily.

I am including the code here so I can discuss it but I suggest you then create a .bas file out of it and import that into new files as necessary.

Here is the code (again please forgive the lack of indents and line continuations … but you can make it work 😉 )  

Copy the code below into a new Module and then run the macro in it “PowerBI”  …….

Dim PT As PivotTable
   Dim Target As Workbook
   Dim intSheet As Long
   Dim c As Range
   Dim LO As ListObject
   Dim LO2 As ListObject
   Dim conn As Variant

'Created by DMoffat dick@plogic.ca 09/29/2016

Sub PBILocal()

'<< Local code required to prep for Publish to PBI >>

End Sub

Sub PowerBI()

 On Error GoTo PBIErr

 PBIInstantiate

 Application.ScreenUpdating = False

PBILocal

Set Target = Workbooks.Add
   intSheet = 1
   ThisWorkbook.Activate

 Set LO = Worksheets("PBI_Main").ListObjects("tblPBIWorksheets")

 For Each c In LO.DataBodyRange.Columns(1).Cells
    If Not IsEmpty(c) Then PBIExcelCode
   Next c

 PBIInfo

 Application.ScreenUpdating = True

 Target.SaveAs Range("'" & ThisWorkbook.Name & "'!PBIExcelTarget").Value & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & ".xlsx", FileFormat:=xlOpenXMLWorkbook
   Target.Close False

 Range("PBILastPublished").Value = Now()

 ThisWorkbook.Save

 MsgBox "PBI Update Complete", vbInformation + vbOKOnly, "PBI Update"
   Exit Sub

PBIErr:
   MsgBox Err.Description & " - Please report this error to your Adminstrator immediately.", vbCritical, "PBI Update Error"
   Application.ScreenUpdating = True
   Exit Sub

End Sub

Sub PBIInstantiate()

Dim WS As Worksheet
   Dim boolFound As Boolean

 boolFound = False
   For Each WS In Worksheets
   If WS.Name = "PBI_Main" Then
   boolFound = True
   Exit For
   End If
   Next WS

If boolFound = True Then
   Exit Sub
   Else
   ThisWorkbook.Sheets.Add , Worksheets(ThisWorkbook.Worksheets.Count)
   With ActiveSheet
   .Name = "PBI_Main"

 With .Cells(3, 1)
   .Value = "PBIExcelTarget"
   With .Offset(0, 1)
   .Name = "PBIExcelTarget"
   .Interior.ColorIndex = 20
   .Value = "<<URL to OneDrive or SharePoint Folder>>"
   End With
   End With

 With .Cells(4, 1)
   .Value = "PBITargetFile"
   With .Offset(0, 1)
   .Name = "PBITargetFile"
   .Interior.ColorIndex = 20
   .FormulaR1C1 = "=PBIExcelTarget&PBIGetFileName()"
   End With
   End With

 With .Cells(5, 1)
   .Value = "PBILastPublished"
   With .Offset(0, 1)
   .Name = "PBILastPublished"
   .Interior.ColorIndex = 20
   End With
   End With

 With .Cells(6, 1)
   .Value = "PBIPassword"
   With .Offset(0, 1)
   .Interior.ColorIndex = 20
   .Name = "PBIPassword"
   End With
   End With

 .Cells(8, 2).Value = "WorksheetName"
   .ListObjects.Add(xlSrcRange, .Cells(8, 2), , xlYes).Name = "tblPBIWorksheets"
   .Cells(8, 1).Value = "tblPBIWorksheets"
   .Columns(1).AutoFit
   End With

 End If

 ActiveSheet.Buttons.Add(20.25, 0.75, 85, 20).Select
   Selection.OnAction = "PowerBI"
   Selection.Characters.Text = "Publish To PBI"
   Cells(1, 1).Select
   End

End Sub

Sub PBIExcelCode()

 Dim TSheet As Worksheet

 If intSheet = 1 Then
   Set TSheet = Target.Worksheets(1)
   intSheet = 2
   Else
   Set TSheet = Target.Worksheets.Add
   End If

TSheet.Name = c.Value

 If ThisWorkbook.Worksheets(c.Value).PivotTables.Count = 1 Then
   Set PT = ThisWorkbook.Worksheets(c.Value).PivotTables(1)
   PT.RowRange.EntireRow.Copy
   ElseIf ThisWorkbook.Worksheets(c.Value).ListObjects.Count = 1 Then
   Set LO2 = ThisWorkbook.Worksheets(c.Value).ListObjects(1)
   LO2.Range.Copy
   Else
   MsgBox "Worksheet " & c.Value & " does not have the single Pivot Table nor Table that is required to Publish. Publish Cancelled.", vbExclamation + vbOKOnly, "Publish Failed"
   End
   End If
   With TSheet
  .Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
   .ListObjects.Add(xlSrcRange, TSheet.Cells(1, 1).CurrentRegion, , xlYes).Name = "tbl" & c.Value
   .Cells(1, 1).CurrentRegion.Columns.EntireColumn.AutoFit
   .Protect Password:=Range("PBIPassword").Value
   End With

End Sub
  Function PBIGetFileName()

If Right(Range("PBIExcelTarget").Value, 1) = "/" Then
   txtSlash = ""
   Else
   txtSlash = "/"
   End If

 PBIGetFileName = txtSlash & Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, ".xls")) & "xlsx"

End Function

Sub PBIInfo()

Set TSheet = Target.Worksheets.Add
   TSheet.Name = "Info"
   With TSheet
   .Cells(1, 1).Value = "Created"
   .Cells(2, 1).Value = Now()
   .Cells(1, 2).Value = "CreatedBy"
   .Cells(2, 2).Value = Environ("username")
   .Cells(1, 3).Value = "Source"
   .Cells(2, 3).Value = ThisWorkbook.FullName
   .ListObjects.Add(xlSrcRange, TSheet.Cells(1, 1).CurrentRegion, , xlYes).Name = "tblInfo"
   .Cells(1, 1).CurrentRegion.Columns.EntireColumn.AutoFit
   .Protect Password:=Range("PBIPassword").Value
   End With

End Sub

Let’s look at the interesting details of this code:

1. If there is no Worksheet called “PBI_Main” then the code creates it the first time. Of course you could remove this loop once the Worksheet is created but it just loops back after you’ve run it once anyway, so no cost going forward. This is what you get:
2. In cell B3 (PBIExcelTarget) you copy in the actual fully qualified URL to your OneDrive for Business Folder.  This of course can be a subfolder there which would be part of the segregation of your PBI reports between various functions and departments within PBI. This cannot be the local synch folder … it MUST be the HTTPS:// xxxxx folder itself.  Once you do this the cell below will automatically show you the name that this code will save up there.  It is the same name as the source file – but as an XLSX rather than as an XLSM.
3. If you wish to, you can type a password in the PBIPassword cell.  This will then protect the file to be used by PBID with a password only someone with this source file will know.  This has no impact on PBID’s ability to retrieve the data from this file BTW.
4. In tblPBIWorksheets you simply list the names of the Worksheets you create where you put your Pivot Tables and/or Data Tables (Listobjects) that you want to Publish.
5. In Sub PBIExcelCode() the VBA code looks for each worksheet listed in the table “tblPBIWorksheets” and then it looks for a single Pivot Table and if not found it looks for a single Listobject (Data Table) in that same Worksheet.  So make sure that each of these publish data Worksheets has only ONE of either.
6. If the data is in a Pivot table the code copies only the rows that are in PT.RowRange.EntireRow. This way if you do create this PT in such a  way that it has Pivot Pages they will be ignored in your Published data .. only the Table of data will be exported.
7. Remember to make sure that your Pivot Tables are in Outline form, that there are no Subtotals in your PivotTable nor any Row or Column totals and “Repeat all labels” is applied.  The same thing applied to any Data Tables/List Objects as well.  JUST Raw data…
8. Make sure that you have access to the OneDrive folder you are targeting.  It is best if you make sure that you can connect to the OneDrive for Business folder before running this VBA macro…

And that’s pretty much it (if you already read the first posting here…).

Next Steps …

I will next blog to you EXACTLY how to import this data into Power BI Desktop (although this really is no different than I posted before)… and how to Automate the refreshing of this data simply by rerunning this code again if the Data changes from your source files.

Then I will be posting how to create “Row Level Security” or RLS in order to make sure that a particular user of your PBI site gets the data THEY need and ONLY the data they need (or are allowed to see) simply by opening one PBI Dashboard or Report.

THEN I will make the argument for why this technique opens up Power BI for use by the 600-700 million Excel users out there 🙂 …

Even if we get to only 0.01 % of them that would still be 70,000 … which will likely be more PBI developers than PBI Desktop will generate on its own using Data Gateways or SQL Azure Databases.  Regardless this is a BIG opportunity for those who create Excel “solutions” for their companies.

Dick