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 email@example.com 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:
- 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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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…
- 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.