Modeloff GTC in New York City – Dec 4-5, 2017

Just a note that I will be in New York on Monday and Tuesday Dec 4 & 5 exhibiting and presenting at:

Modeloff GTC New York

I will be showing the latest iteration of the XLPublish add-in on exhibit with several new and exciting capabilities (including our new CUBE feature). I will also be discussing the entire issue of getting Excel data into Power BI (which will of course include using XLPublish) in an Hour+ presentation on Tuesday afternoon.

Hopefully you are signed up for it and will drop by and say hi but if you are in the area and want to get the latest on Excel Financial Modelling this is the place to be …

Dick

Advertisements
Posted in Business Intelligence, Excel, Excel 2013, Office Automation, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Spreadsheets, VBA, XLPublish, XLPublish.com | Leave a comment

XLPublish Is Here !!!

Today Personal Logic Associates Inc. is proud to ship XLPublish – our new Add-in for Excel that makes publishing your Excel data in Power BI Easy, Reliable and Repeatable.

With XLPublish you can make your current Excel Ranges, Tables, Pivot Tables and Charts on-going sources of Power BI Datasets. Without installing a data gateway! So you can start taking advantage of all the cool features of Power BI right away ūüôā XLPublish comes with native unpivot, append and consolidate functions, easily integrates with Get & Transform and Power Pivot and supports multiple sources feeding a single Power BI dataset.

Optionally, you can push your data directly to a Microsoft SQL Server or a Microsoft Azure database.

Installing XLPublish is easy. Download a 30-day free trial at:  

https://lnkd.in/gHiZGKX 

and see for yourself. 

If you like it, please tell us what you think/like this/share this..

http://XLPublish.com

Posted in Business Intelligence, Excel, Excel 2013, Microsoft Excel 2013, Office 2013, Office Automation, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Spreadsheets, XLPublish, XLPublish.com | 1 Comment

XLPublish Is Almost Here !

 

 

This is a heads-up about the imminent release of the new XLPublish add-in written by myself and David Hoppe.

If you go to our Website ( xlpublish.com )  there is an explanation of what this is.

We will be having an official launch on June 28.  Please watch for it.

Dick

Posted in Business Intelligence, Cloud, Excel, Excel 2013, Power BI, Power BI Desktop, Power Query, PowerPivot, Process Automation, Spreadsheets, the Cloud, XLPublish, XLPublish.com | 3 Comments

How To Really Publish Excel Data To Power BI … Part Deux (amended again)

It has been brought to my attention that  the formatting I chose for my Blog post on this makes for a difficult to copy and paste the code I listed into an Excel module without significant effort (like fixing doub;t-quotes, single quotes and unrequested spaces, etc.)

I am very sorry about that.

I have fixed that issue with an alternative format on the original posting here:

https://dmoffat.wordpress.com/2016/09/29/how-to-really-publish-excel-data-to-power-bi-part-deux/

But here is is again ..

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

This code should copy and paste directly into your module.

Then run the “PowerBI” procedure in that code to configure you model. ¬†Then copy and paste the target SharePoint folder that you will be Publishing to in the PBIExcelTarget Cell in the format:

https://yourconame.sharepoint.com/Shared Documents/PowerBIFolderName

Excuse this please.. and enjoy ..

Dick

Posted in Business Intelligence, Cloud, Excel, Office 2013, Power BI, Power BI Desktop, PowerPivot, Spreadsheets, the Cloud, Uncategorized, VBA | 8 Comments

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

OK .. Let’s¬†go … Part III … !!

In this post I will try to explain how (and especially how not) to get your Excel data into Power BI Desktop in a way that will allow you to schedule refreshes like a normal person … so that all you have to do is run a little Excel VBA code to export your data to Sharepoint (note I said Sharepoint not OneDrive … I’ll explain) and then at an acceptable scheduled time of day it will automagically refresh your data up in PowerBI.com and your consumers of your Dashboards will see the latest and greatest data …

The fact is that if you use the obvious available menus to connect to your Excel data IT WILL NOT WORK … and for reasons like I will explain (but not justify).

How NOT To Connect Excel Data Into Power BI

I considered only showing the positive here and only showing what DOES work … but in the end I think there is enough confusion in this topic that I have to show the bad and the good.

Using OneDrive

In all the literature on Power BI OneDrive for Business is constantly referred to as THE place to store Excel data for use in Power BI. ¬†There is no doubt that OneDrive for Business does work for PBI and PBID BUT the fact is that OneDrive for business is actually a subset of SharePoint in the first place. ¬†If you want to use OneDrive for Business as the “staging” area for your PBID Excel files you have to make sure the connection is to the ACTUAL URL in Sharepoint that gets you to the file… i.e.

https://<<Company name>>-my.sharepoint.com/personal/YourLogin_onmicrosoft_com/Documents/Folder1/USElection2016 Direct OneDrive.xlsx

Notice that this is in fact a Sharepoint site that exists under YourLogin name … ¬†This means that if you quit, get promoted, fired, retire or die this site will go away with you … oops.

On the other hand, if your company uses Office 365 they automatically receive the ability to easily create and manage (including security of all types) the ability to create “Company” departmental SP sites at will that are maintained by an Admin(s) who can change and be managed easily any time. ¬†Yes OneDrive can do all this but why not just sue SharePoint? ¬†I don’t get this and would really like someone to tell me why the obsession with OneDrive and not Sharepoint (must be political inside MS I guess).

So in my future ALL my solutions are going to migrated to SharePoint from OneDrive for Business … unless someone convinces me of the advantages of using OneDrive .. and switching back would be fairly easy too.

Using the Get Data … Excel Menu-pick in PBID

This is a really weird one … This menu item simply does not work, never has and I assume never will.

Oh sure you can use Get Data РExcel to link to internal files inside you firewall.  But if you do so, you will be forced to ALWAYS open the PBID file on a network PC with access to the sources in their original network location every time the Excel data changes and then you have to republish the PBID file to PowerBI.com.

That is unless you just distribute the PBIX file (which I know many do) which is frankly ludicrous and pretty much defeats the whole advantage of Power BI to deliver to any device anywhere and to refresh data automatically simply by changes to the data source itself. ¬†Oh sure there will be those who argue for this for security reasons, but that is bogus IMHO, and probably reflects the fact that they can’t figure out how to do what I’m gonna show you.

So why not use this menu item to connect to an Excel file in SP or OneDrive for Business? NOT

OK … say you path your Get Data – Excel to the actual URL out in :”the Cloud”? ¬†Like

https://<<Your Company Name>>.sharepoint.com/Shared Documents/PowerBI

and then you pick the file from there? ¬†Well …. Hmmm … ¬†If you do that PBID picks up the path to your file in this format:


excelimport

You will be forced to edit out any extraneous references in there so you end up with a Source reference like this:

= Excel.Workbook(File.Contents(“\\Your Company Name.sharepoint.com\Shared Documents\PowerBI\ImportSales.xlsx”), null, true)

If you publish THIS link to PowerBI.COM it fails because it will be looking for a Windows account for access and there is none for SharePoint or OneDrive for Business.  It simply will not refresh unless you do it manually.

Do not ask me why it does this, but the fact is that the path here is useless except to import your data ONE TIME ONLY!  It will not refresh under any circumstances.  Oh sure you could let it fail and then edit the path by copying in the full URL at that time and it MIGHT work (after you edit the security in PBI).  But that is a lot of work and it actually imports very slowly to boot.

So do NOT use the Get Data … Excel menu pick

Use the Get Data … Web dialog instead !!

Choose Get Data … Web and paste the fully qualified URL path to your SP file in THIS¬†dialog

excelimport

then you will be able to schedule auto-refresh when this PBID file gets published to PowerBI.com … This is the reason why in the code in my previous post(s) on this topice I have added a cell where you can put the fully-qualified reference to your sP site and also a formula which shows (therefore you can copy it into the clipboard) the actual target Excel file that will be created when you run the code. ¬†You simply have to leave this file open and copy and paste the reference to the source Excel file in PBID when this dialog appears … simple eh?

(Interesting weird fact … recently this process failed for me as PBID insisted on adding extra characters to the end of my URL after the file name (?) ¬†I have no idea what that was about but it appeared to have something to do with having the actual site that the file was in open in the browser in SharePoint. ¬†This was new but I resolved it by closing the browser completely while I was working in PBID…. This was disconcerting but I believe that it is not an ongoing problem .. Today while I am writing this I have everything open and it all works fine … Hmmmm. ¬†Problem is that I have no idea who I would reach out to about this if it becomes an ongoing issue… It does not please me even though I can work around it …)

And these refreshes will happen automatically to bring in any changes made to the source EXCEL file … NOT to the PBIX file that is saved in PBID … the Excel file. ¬†This means you users do not need to know anything about the plumbing of Power BI .. they just need to open and refresh or manually change the data in the file and either run the VBA to Publish the data to SP OR you could even include that code into the OnSave property of the Workbook. ¬†Automatic Refreshing all the way through ….

I know this is hard to imagine and grasp. ¬†It is REALLY hard to explain as well which is a great source of frustration for me… ¬†But as I will show you next, this works and the other simply does not.

This all makes my brain hurt.brainhurts

 

 

Set Up The Auto-Refresh In PowerBI.com

If you use the techniques I mention just above, once you have published the PBID’s PBIX file to PowerBI.com you still have a couple more things to do. Remember that PBI.com lives in “the Cloud” and for it to “talk” to your data in SharePoint you have to provide credentials for PBI to use tp reach the data. ¬†This is done only in PBI (not PBID) and is actually quite simple 9if quite hard to find unless you know what and why you’re looking for it).

  1. Open Power BI in the browser (app.powerbi.com) and move down to the DataSets. ¬†Find the Dataset you JUST created and right-click the ellipsis on its right and choose “Schedule Refresh”… ¬†There are 2 things you have to do there…
  2. Open “Data Source Credentials” and choose “Edit Credentials”, click in the middle of the drop-down box of “Authentication Methods” (the pulldown menu simply does not work so you have to click in the middle of the text box (literally) to get the list of protocols and select “oAuth2”. ¬†Why “oAuth2” ? ¬†I assume that this is the protocol to connect to Cloud-based data sources like SharePoint … but to say this is NOT INTUITIVE is quite the understatemtn ..
  3. When you “Sign In” with this it will ask you to confirm your login to SharePoint. ¬†Click your account. ¬†Once you have done that you will be connected to the source file in SharePoint in “the Cloud” !!excelimport
  4. Then click on “Schedule Refresh” and click to “Yes” the “Keep your data up to date”.
  5. If you leave it this way it will refresh daily … but you can now add up to 8 times during the day to refresh the data … (of course you could also use “Refresh Now” to force a refresh). ¬†I hope some day we can sinply say “Every hour or ever half hour but this is more tha adequate for 99% of solutions, I believe. ¬†This is IN THE BROWSER NOT IN THE PBIX FILE btw !!excelimport

Conclusion

So there ya go …

Now you can go back to PBID, open the PBIX file and add whatever Charts, tables, images or any of the cool stuff that you can do in Power BI.  Remember to DO IT ALL IN PBIX file in PBID, save that file locally and then re-publish it to PBI.COM.  Yes you can change things up in PBI.COM but why bother ?  Have a single point of development.

Why this is so insanely complex I have no idea …

Why this is effectively NOT documented anywhere I also don’t know either ūüė¶ …

Why the Excel team isn’t all over this is absolutely beyond my comprehension but frankly nothing surprises me anymore from them… sorry guys and gals… Prove me wrong … please !!

But the fact is that by using these techniques you will be able to publish Excel data (including but not limited to data expressed in a Pivot Table) in Power BI though Power BI Desktop and with auto-refresh happily updating your Dashboards and Reports on a schedule of up to 8 times every day or manually through your browser.

I will next show how to customize your Power BI solution for each individual or group of consumers of your data … again using Excel as the natural platform.

I will also discuss details of how to build a combined Excel/PBID solution using relationships, DAX and KPIs using the best technique in the best environment.

I will also discuss how you can design your PBI implementation bring this data BACK INTO Excel using the Power BI Add-in for Excel in an effective way.

Yes I think that this is exciting stuff and yes I am willing to talk about it to anyone anywhere so feel free to reach out to me for guidance or help.  This is the most exciting thing in Excel since Power Query and before that Power Pivot.

I believe that it’s about time everyone got on board all the effort that the Excel Team has put into their product (including the Excel Team themselves btw) and we must all get started to leverage all this great tech for the benefit of our Companies and/or Clients and also benefit¬†ourselves (frankly).¬†

Dick

 

Posted in Analysis Services, Business Intelligence, Cloud, Excel, Excel 2013, Microsoft Excel 2013, Office 2013, Office Automation, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Spreadsheets, the Cloud, Uncategorized, VBA | Tagged , | 4 Comments

How To Really Publish Excel Data To Power BI … Part Deux (amended)

Just a quick note for those that looked at and perhaps tried out the code in the post of a couple of weeks ago ( https://dmoffat.wordpress.com/2016/09/29/how-to-really-publish-excel-data-to-power-bi-part-deux/)  .  

I have made a very small but very important change to the code.

Turns out that when I push the limits (whatever those are?) by publishing data table with lotsa data (my recent test was 500,000 rows) the code basically grinds to a halt ūüė¶ ..

This is because in the code while I paste xlValues into the target worksheet, when I paste xlFormats it take many minutes to complete the paste … that is because there are the formats in the source Pivot Table to paste and this take s loooooong time to complete.

So I have amended the code to paste only Values and Number Formats .. and it does it virtually instantly even with a large number of rows.

So you need to replace this:

.Cells(1, 1).PasteSpecial Paste:=xlValues
.Cells(1, 1).PasteSpecial Paste:=xlFormats

with this:

.Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

in Sub¬†PBIExcelCode()¬†….

Sorry I missed that …

I will be posting about the next steps this week,

Dick

Posted in Business Intelligence, Cloud, Excel, Excel 2013, Office 2013, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Spreadsheets, the Cloud, Uncategorized, VBA | Leave a comment

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:pbi_main
  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

 

Posted in Business Intelligence, Cloud, Excel, Excel 2013, Office 2013, OLAP, Power BI, Power BI Desktop, Process Automation, Spreadsheets, the Cloud, Uncategorized, VBA | 2 Comments