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

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


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\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


then you will be able to schedule auto-refresh when this PBID file gets published to … 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

If you use the techniques I mention just above, once you have published the PBID’s PBIX file to you still have a couple more things to do. Remember that 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 ( 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


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). 



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 , | 2 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 (  .  

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,


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 ( ) 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 09/29/2016

Sub PBILocal()

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

End Sub

Sub PowerBI()

On Error GoTo PBIErr


Application.ScreenUpdating = False


Set Target = Workbooks.Add
intSheet = 1

Set LO = Worksheets(“PBI_Main”).ListObjects(“tblPBIWorksheets”)

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


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()


MsgBox “PBI Update Complete”, vbInformation + vbOKOnly, “PBI Update”
Exit Sub

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
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”
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 Sub

Sub PBIExcelCode()

Dim TSheet As Worksheet

If intSheet = 1 Then
Set TSheet = Target.Worksheets(1)
intSheet = 2
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)
ElseIf ThisWorkbook.Worksheets(c.Value).ListObjects.Count = 1 Then
Set LO2 = ThisWorkbook.Worksheets(c.Value).ListObjects(1)
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 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 = “”
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.



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

How To Really Publish Excel Data To Power BI

OK .. I’m going to try to make this as short as I possibly can. I am sick of docs that do not explain to people how to REALLY accomplish something😦  … I want this to be different but still as short as possible …

I have spent a long time deciding whether to keep this to myself and try to monetize it to my own benefit.  But I realized, in the end, that I would end up a lonely guy with no business from this .. because it is seriously at odds with the “Official” Power BI orthodoxy.

Why do I keep doing that😦 ?

So here ya go … Hello World !!  Hopefully I will be able to figure out some way to make some money on this anyway …


If you are a serious Excel developer who has made the effort to learn Power Pivot and/or PowerQuery/Get & Transform I strongly suggest that you really can’t publish detailed source data from Excel Data Model tables to Power BI to be aggregated there (and shouldn’t if you could anyway )… Instead you can create Tabular Pivot Tables in Native Excel and publish JUST THOSE RESULTS to be used by Power BI.  I will show you how here. We can discuss WHY another day … Trust me ….🙂

I have spent months and months in Power BI by now.  I am sure that I am NOT the only person tearing their hair out from reading page after page after page of words, words and more words that fail to do anything but confuse the heck out of me about what is theoretically possible in Power BI … while almost never giving me what I really need to know to get something actually working up there …

And every day it seems there’s a new theoretical data source from some other obscure tech that I have zero interest or need to know but still no answers to the REAL questions that I have.

If you’re interested in the story of my Power BI “Odyssey” I have given several on-line presentations on it and I would consider doing the same for you if you have an interesting group for me to talk to.

So I am going to give you one simple solution today for a specific group of Excel users (or at least as simple as is possible in Power BI).

( I refer to as PBI & Power BI Desktop is PBID)

Here we go …


  1. You have an existing Excel file with the information from external data sources or even from in-file tables in the Data Model via Power Pivot or Power Query/Get and Transform.
  2. You know Pivot Tables
  3. You have either a OneDrive for Business account OR you have access to publish to a SharePoint Site out in “the Cloud” somewhere
  4. You have Power BI Desktop (PBID) installed on your PC locally …, while identical to PBID in the UI department, has no ability to manage data sources other than managing refresh (which is a nightmare of confusion and misinformation).  It is only useful for Publishing your work done in Excel and PBID.
  5. You and all your potential consumers of your “Dashboards” have at least free PowerBI.COM (PBI) accounts individually
  6. If you have any problems with any of this I would be glad to teach it to you or your company

Step 1 … Prep your data

Open your Excel file that includes your Data Model and everything you have built to create your solution.  We will be building a mechanism in that file to export to PBI.  You then repeat what I am going to show you in any file you wish to integrate into PBI.

On a blank Worksheet named to the name of the table you want to have in PBID (i.e. “tblMonthlyLabour “), create a single Pivot Table.   Here is an example of a Pivot Table of aggregated data:


Notice that it is pre-aggregated by End of Month Date (why would you need detailed records in a report like this?), and does not include names (which is a security issue).  It is strictly the data for a specific report.  Sure I could include more levels of detail but the point is that this data is pre-aggregated… reducing thousands of records to a few … I can also create and publish a “Dimension” table at the same time and redice this table to just the numbers and a foreign key (which you could call a “Fact Table”), then join them with a relationship up in PBID to be more efficient.  But this denormalized example works here to demonstrate the connectivity I am trying to explain.  Purists please don’t jump on me for this sacrilege.  

It is my opinion that since PBI is for “Analytics” or “Reporting” and that as such data shown is intended not to be shown at the lowest granularity of data available. For example your reports should show data summarized by critical increments like Daily, Monthly, Quarterly or annually and certainly do not require that you have available all data since the beginning of recorded time🙂 …

I personally ALWAYS import ONLY the time period I am reporting on (i.e. “Current Year” or “Current Year” compared to “Previous Year”, etc. If necessary I will even pre-aggregate my source data in SQL Sever Functions and connect to THAT data initially, adding more information in Excel before publishing to PBI. Again if that interests you, I would be willing to discuss how to do that with you (or you can search my previous blog posts to find what you are looking for).

To make this work you need your Pivot Table to “Show in Tabular Form” and “Repeat All Item Labels” and eliminate all Row and Column totals so the result is a Tabular Pivot table and is laid out exactly as a table and make sure there are no sub-totals on any row or column … “Plain Vanilla”

(Also make sure you use the exact names I describe here in order for the small amount of VBA code to work … You can change anything you want but will need to revise the VBA accordingly – of course.)

Step 2: Capture the Target URL

Create a new blank Worksheet in your file.  Name it “OneDrive”.

Find out the actual URL of the OneDrive Folder you are going to use to store your data.  i.e  “” .. By this I mean the EXACT URL as above, NOT a local synching folder … You’ll see why later.

You MUST have accessed this Folder from your local machine and must have saved the password unless you want to retype the password every time .. your call.

Paste this URL into a cell in this new Worksheet near the top in Column B.  Then name that cell “ExcelTarget”.  If your target ever moves just replace the contents of this cell with the new URL.

In a new Module (I call it “modPBI”) copy in this code:

Function FileName()

FileName = Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, “.xls”)) & “xlsx”

End Function

In the cell below “ExcelTarget” type this formula:


Then name that cell “txtTargetFile” …

In this way you have a consistent Target file name based on the name of the Excel file you are publishing from and also you have easy access to the fully qualified path to it.  This ends being REALLY important later inside PBID.

Step 3: List of Pivot Tables To Export

A couple of cells below txtTargetFile create a simple one column Table called “tblOneDrive” with one field entitled “Pivot Table” :


You will list the names of the Worksheets you want to export to PBID .. Remember each of these Worksheets will have only one Pivot Table and nothing else on them …

Step 4:  Create a little VBA

I certainly hope that adding some VBA isn’t a problem for many and don’t get me going on why that may be so.  I believe that VBA is critical to using Excel and that while PowerQuery/G&T is great, I do not believe that it will also cure cancer and bring peace to the Middle East at the same time as we are told by the powers that be …

But I guarantee that this code is VERY straight forward and truly “cookie-cutter” so it can be used intact in any file you want to use as a source for PBID. I assume that anyone doing PP or PQ/G&T has to know about VBA to some extent…

So copy the following code into the same VBA Module (modPBI) that you copied the “FileName()” function into.  (Please forgive that my nice indenting disappeared in the copy but do not think any less of me please) :

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

‘2016 – By Dick Moffat Personal Logic Associates Inc. –

Sub PowerBI()

On Error GoTo PBIErr

Application.ScreenUpdating = False

For Each conn In ThisWorkbook.Connections
Next conn

Set Target = Workbooks.Add
intSheet = 1

Set LO = Worksheets(“OneDrive”).ListObjects(“tblOneDrive”)

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

Application.ScreenUpdating = True

Target.SaveAs Range(“‘” & ThisWorkbook.Name & “‘!ExcelTarget”).Value & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) – 5) & “.xlsx”, FileFormat:=xlOpenXMLWorkbook
Target.Close False

MsgBox “PBI Update Complete”, vbInformation + vbOKOnly, “PBI Update”
Exit Sub

MsgBox Err.Description & ” – Please report this error to your Administrator immediately.”, vbCritical, “PBI Update Error”
Application.ScreenUpdating = True
Exit Sub

End Sub

Sub ExcelCode()

Dim TSheet As Worksheet

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

TSheet.Name = c.Value
Set PT = ThisWorkbook.Worksheets(c.Value).PivotTables(1)

With TSheet
.Cells(1, 1).PasteSpecial Paste:=xlValues
.Cells(1, 1).PasteSpecial Paste:=xlFormats
.ListObjects.Add(xlSrcRange, TSheet.Cells(1, 1).CurrentRegion, , xlYes).Name = c.Value
End With

End Sub

Now just add a button to the worksheet and make “PowerBI” the assigned Macro.  Make the caption on that button say something about “Publish To OneDrive” or some such thing that makes sense to you.

I hope you have saved the file as you went along but for certain SAVE IT NOW !!

Step 4:  Run the macro and debug it ’til it works for you.

No explanation needed here ..

Step 5: Open PBID and Create a New PBIX File

  1. Before you open PBID select the “txtTargetFile” range in your Excel file and copy the value there to the clipboard
  2. Open PBID and start with a new blank file.
  3. Do NOT select “Get Data … Excel” … Without some serious convolutions this will not work … trust me … don’t go there.
  4. Instead Choose “Get Data … Web” … leave it at Basic and paste the link you have in the clipboard as the URL

tblOneDrive25. When the list of objects in the file appears, select the table(s) you wish to import … (notice that the actual Tables to be imported have the prefix “tbl” which was added to the name of the table to differentiate it from the Worksheet name.  You COULD choose the Worksheet name too I guess but this is how I do it🙂 .. )

6.  Save your PBIX file NOW !!  Before doing anything else.  When the Save To Dialog comes up just paste the path that is still in the clipboard and remove the “.xlsx” characters from the end. IT IS IMPERATIVE THAT THE PBIX FILE IS KEPT IN THE SAME ONEDRIVE FOLDER AS THE SOURCE EXCEL FILE THAT THE VBA CODE CREATED !

7.  For some reason this file you just created does NOT show up in the list of Recent Files in PBID (?) So to get at the file you either have to path to that folder or once again borrow the path from your Excel file. This is one of the MANY quirks in PBI and PBID which I am trying to cut through here.

8. Make ABSOLUTELY SURE that the PBIX file was successfully saved to the target OneDrive folder,  I suggest you close the file and reopen it immediately !!  I have seen the initial Save fail and produce a 0 kb file.  That is why I believe in saving the PBIX file BEFORE creating any Charts, Reports, Dashboards, etc.  Once the file is created initially it has never failed to save for me … But we all know the saying that “He who laughs last made a backup.

Step 6: Publish the PBIX file to PBI.COM

Before creating any sexy charts or tables (you will re-publish this PBIX many times and everything will be fine) You must Publish the PBID file you just created to PBI.COM by clicking the PUBLISH button in the upper right.  Select you Workspace (eventually you will set up multiple Workspaces up there but for now just use My Workspace) and save it there.  Then follow the link in the Dialog to go to your PBI.COM site.

Step 7: Set Up Your Refresh Schedule In PBI.COM – ABSOLUTELY IMPORTANT !!!

Many thanx to my friend Oscar Broekman for solving this final piece of the puzzle for me .. I personally spend a couple of months (literally) trying to get an answer on this from PBI support (despite their best honest efforts) only to be in a conversation with Oscar and he said something to the effect of “Yeah … we had the same problem … So when in Redmond I asked a PBI dev about it AND HE TOLD ME HOW TO DO IT !!!”  AAAAAAGGGHH !

In you will see both a Report that is the same name as your PBID file (PBIX extension).  But there is also a Dataset defined too under the same name.  It is the Dataset that is most important at this stage … and is most important overall.

Go down to the Datasets and click the ellipsis to the left of the Dataset you just created.


In this dialog select “Schedule Refresh”

Choose “Data Source Credentials” in the dialog that appears and then click on “Edit Credentials” …



You click in the middle of the “Authentication Method” list (I find that the Scroll Bar on the right interferes with the pull-down arrow (maybe that’s only in my resolution)… AND SELECT “oAuth2“…  Apparently this is the protocol for connecting to Web data sources … who knew ?😉 ..

Then go Sign In … You should then be taken to your Office365 login which should have your login selected (if not then “Use Another Account”) … and LOG IN !!

By dowing this last LOGIN you are telling PBI.COM to use YOUR credentials to allow it to reach into ONEDRIVE to get to the PBIX file you created in PBID… And by making sure that your original Excel file is ALSO in the same Folder and its Path is exactly the same as the PBIX file PBI.COM WILL BE ABLE TO REFRESH YOUR DATA ON A SCHEDULE YOU DEFINE BELOW !!!  WITHOUT THIS LAST STEP IT WILL NEVER REFRESH … NEVER !!

Now click on “Schedule Refresh” and you can add as many times in a Day you wish (on the half-hour) …  Or you can manually go “Refresh Now” from the same ellipsis at any time WITHOUT HAVING TO REPUBLISH THE PBIX FILE … YOU ONLY NEED TO REDO THE EXCEL REFRESH THAT WILL POST A NEW SET OF EXCEL DATA TO ONEDRIVE .. IF YOU TIE YOUR PUBLISH IN EXCEL TO THE SAVE OF THE EXCEL FILE YOUR USERS DON’T EVEN NEED TO KNOW IT’S HAPPENING.


Step 7: Have fun creating your PBID Dashboards and Republishing Whenever You Make A Change

Keep in mind that even when you republish the PBIX file from PBID the Refresh Scheduling is preserved.  Do NOT change anything in PBI as it will only be overwritten by the next PBID re-Publish anyway.


In actual fact this was a SHORT description of this technique (I would like it to be called “The Dick Moffat Technique”😉  ).  Sorry for all the BOLD face but those comments are completely important.

This was written strictly for the use of those who already use the Data Model in Excel.. My assumption is that someone who goes to that much effort (and believe me it IS EFFORT) is likely to have looked into PBI and has likely thrown their hands up in disgust or accepted some kludgy Plan B or C scenario to get their info up there.  On the other hand even if you don’t use the Data Model you can use this technique to publish regular Native Excel pivot table data and with the replacement of :

Set PT = ThisWorkbook.Worksheets(c.Value).PivotTables(1)


Set LO2 = ThisWorkbook.Worksheets(c.Value).ListObjects(1)

in your code you can publish Table data as well.  (I personally have built it so you can select which type of Object it is in the tblOneDrive and branch accordingly in my own code.)

But the BIG story here in the end is the fact that this technique can be used by ANYONE wanting to create a PBI solution.  By just using Excel as Middleware a person can connect to ALL the internal data sources that they are allowed to get to (which in itself is usually a problem), create the necessary tables of aggregated data that they want to publish and then push that data up to OneDrive for use by PBI.

No Data Gateway, no exposure of detailed Corporate data to “the Cloud” (regardless of how secure it really is anyway) .. quick refreshes and frankly much easier development inside PBID (where you can add further DAX Measure to calc things like running totals, etc.).

I will soon also show how you COULD use this same technique and publish the data to an Azure SQL database in the Cloud if so inclined.  To me it’s six of one half dozen of another but frankly I think that’s what Microsoft would like us to do .. Do I need to remind you that Excel isn’t cool🙂 …

So maybe “The Dick Moffat Technique” will revolutionize PBI and as result will expand the reach of all those millions and millions of Excel Month-End and Production reports out there.  I wish !!

Otherwise I believe that there’s a risk that PBI will end up in a narrow band of BI Nerdness …

If any of you want my assistance on this I am available as a consultant BTW …  You know how to find me …🙂

Please  give me feedback on this.



Posted in Business Intelligence, Cloud, Excel, Excel 2013, Microsoft Excel 2013, Office 2013, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Spreadsheets, the Cloud, Uncategorized, VBA | 1 Comment

Tech Support Call From 1985

I received THIS request for tech support YESTERDAY …

Hi Dick,

Would you please tell me what a Code A is and how we fix it. When we try to add soy loads to one of the years we get an error message – “CODE A – Call Personal Logic”.


The guy who wrote this email is the accountant at a Grain Dealer (they process Corn and Soybeans and Wheat).  He is the guy who replaced the guy who I sold a Lotus 1-2-3  program to a while back.

You see I sold it to my man Elgin when we were both in our early 30’s.  Elgin retired a couple of years ago so this is his replacement.  You see .. I sold them this program (which basically runs all the receiving, shipping and storage of all commodity for their Grain Elevator) IN 1985

Written in Lotus 1-2-3 it is a data-centric version that is actually a database program that brings data in and out of a code and forms shell.  It has all the capability to start a new year and a new commodity themselves. And they have not called me in about 2 years.  In fact no one called me for the last 10 years (there were still a couple of places using it in the last ten years) unless it was because they forgot how to do something … maybe 3 calls total.

I think I should win some kind of award for this but of course I won’t.  I never made much money on it either but frankly the stuff I learned building the app was my PHD in building reliable, maintainable, self-managing spreadsheet solutions.

Maybe my mistake has been that I build stuff to work and work and work so I don’t make enough money on support or revisions😦

Dilbert Bugs


Posted in Access, Access Solutions, Business Intelligence, Excel, Excel 2013, Microsoft Access, Microsoft Access 2013, Microsoft Excel 2013, Office 2013, Office Automation, Power BI, Power BI Desktop, Power Query, PowerPivot, Spreadsheets, VBA | Leave a comment

How To Change Data Sources Dynamically In Excel With Power Query

My Crow Eating Exercise

This past week I asked on this blog whether anyone out there was using the technique I wrote about years ago for dynamically changing the Source Connections for Power Pivot Tables in Excel.  I have noticed that people keep coming to it but no one ever tells me whether they use it or not.

The only response I received was from 2 Power Query “gurus” named Frank Tonsen and a guy who’s name looks like Sam Hide.  I really appreciate their prodding me to look at finding a way to accomplish the same using Power Query.

They detailed to me that it is apparently possible to accomplish the same in Power Query – something I never looked into as I have been satisfied with what I’ve been using and frankly because i don’t use Power Query aggressively to this point.  I admit frankly everyone doesn’t have Power Query installed with Excel 2013 and IT can be a block … and I don’t deal with anyone who uses Office 2016 to this point.

So I decided to look into this and found as always an endlessly complex and frustrating set of Web hits, none of which really seem to resolve this issue specifically. But in all this were snippets of what I appear to need.

My Demands

There were two issues that have to be satisfied for me to make the switch (which I REALLY WANT TO DO)

1. It had to be straightforward and as simple as possible so that many can implement it and

2. It had to accommodate my requirement to change values in an Excel file and have the Data Sources change based on the UI … In other words the user should be able to pick a particular year or Division and retrieve just the data for that … This is to limit the impact of large data sets creating HUGE Excel files and of course eliminate those looooong refreshes.  To accomplish this I create SQL Server Functions and pass parameters to them like this:

select * from fnDataSummary(’01/01/2015′,’12/31/2015′)

where the dates in this formula:

=”select * from fnDataSummary( ’01/01/”&CurYear&”‘,’12/31/”&CurYear&”‘)”

are driven from a Slicer or a pick list inside the spreadsheet … very cool

My Solution

So here we go … This is a way to connect to a SQL Server or SQL Azure database.  I hope you find this simple .

First I suggest you save as often as you can in this process. I find that when developing in Power Query it does crash from time to time   . This doesn’t bother me as this is not a new thing in Excel and most of the time if you close Excel and reopen the file things go back to normal.  Once the file is complete and functional it appears to stay stable (just like Power Pivot.

Select a SQL Server that you have Windows rights to (for convenience), a database in that Server and a simple Select Statement against a Table in that database.

Create a blank Excel Workbook

Create a simple table like this, make it a table and name it “tblConnections” for our purposes (putting your SQL statement in the SQL Field in row 2):

Launch Power Query and ‘Launch Editor’ …

Create a ‘New Source’ … ‘Other Source’ …’Blank Query’, Open its Properties and rename it qryMasterTable …

Open qryMasterTable in the ‘Advanced Editor’ and Type or copy THIS into that query:


txtSQL= Excel.CurrentWorkbook(){[Name=”tblConnections”]}[Content],

#”Changed Type” = Table.TransformColumnTypes(txtSQL,{{“SQL”, type text}, {“Server”, type text}, {“Database”, type text}})


#”Changed Type”

Create a New blank query, rename it ‘qryData’, open the ‘Advanced Editor’ and copy this text into it:


Source = Sql.Database(qryMasterTable[Server]{0},qryMasterTable[Database]{0}, [Query=qryMasterTable[SQL]{0}])



Go ‘Close and Load’ …

Back in Excel while still on the Power Query Tab choose ‘Options’ … ‘Privacy’ … select ‘Ignore the Privacy ….’ … Click ‘OK’ … (see notes)

In Excel go to the Data Tab and go ‘Refresh All’ …

This should import the data from the SQL that you entered from the Server and Database you put in the tblConnections table.

And that’s it … two very small queries that drive the querying of data.

Important Notes ..

If you want another data source simply add other rows to tblConnections as necessary and duplicate ‘qryData’ and change the references to ‘{0}’ to [1] and then ‘{2}’ accordingly.  The name of each query in Power Query will become the name of the new table.  (You can even rename the tables by renaming the queries btw …).

And remember that any DAX that you place in any of these tables in the Power Pivot Data Libraries are preserved despite all these changes.

There is to me an odd feature (?) of Power Query called “Fast Combine” which is a mechanism designed to basically prevent you from mixing data from multiple sources in one spreadsheet … ?  I am really unsure why this is so (maybe some auditor or lawyer got to the developers and insisted on this).  While this pretty much would make it almost impossible without great effort to integrate multiple data sources into one Excel file (which I often do btw) there is also a way to override this “feature” by choosing to ignore it… Unfortunately this becomes a problem for us here because that little table in Excel is considered an “alternative data source” and so this rule kicks in unless you choose to “ignore” it…  There are all kinds of warnings of the risks inherent in doing this but frankly it strikes me as the exact same kind of overkill as Macro security settings (only MUCH worse).  This is because it is related to each individual user and the settings “Private”, “None” etc.  of every data source and every user has to personally set this to “Ignore” for every new file they get.  It is nothing short of a pain in the ass to me.

Interestingly though, in Excel 2016 they decided to provide a VBA workaround which to me indicates that I am not the only one who thinks this is a bit much in the REAL WORLD…  That is the capability to add this line in the Workbook_Open() Sub:

ThisWorkbook.Queries.FastCombine = True

This allows the Power Query queries to run just like you clicked “Ignore … “

If it’s a big thing you can make the setting FALSE after you do a refresh or even better make this setting only accessible with a password so that the refresh can only be done by an Admin of some kind.

To me this is a BIG reason to get your clients to move to Office 2016 asap.


So as far as I can tell Sam and Frank are absolutely correct that if you’re using Excel 2016 then Power Query and this relatively simple technique will make your solutions far more responsive to the real needs of your solution. I hope this means that you can teach an old dog new tricks and I hope this simple example makes sense to the most possible people so they won’t have to do all the searching I had to do to get this working..

Enjoy …


P.s. Ken Puls did a recent post on this exact issue that somehow was missd in all my searching … It’s a typically thoughtful Ken posting and offers an alternative way to accomplish this :

Pass Parameters to SQL Queries – The Ken Puls (Excelguru) BlogThe Ken Puls (Excelguru) Blog

Pass Parameters to SQL Queries

Posted in Business Intelligence, Excel, Excel 2013, Office 2013, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Process Automation, Spreadsheets, VBA | 11 Comments

How To Change Connection Type In Power Pivot

Every day, day in and day out, the most popular posting I have here is this one:

and this one:

I personally use these techniques all the time, for years now and it is my standard modus-operandi for Power Pivot development in Excel.

Yet despite the fact that everyone comes here every day and looks at this page, I HAVE NEVER heard anything back from anyone whether they actually use this, if it works for them or not .. Not once…

That’s pretty frustrating people …🙂


p.s. go HERE …🙂

Posted in Excel, Excel 2013, Office Automation, OLAP, Power BI, Power BI Desktop, PowerPivot, Process Automation, Spreadsheets, Uncategorized | 19 Comments