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 …

Premise …

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

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

Here we go …

Assumptions

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 … PowerBI.com, 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  “https://personallogicassociates-my.sharepoint.com/personal/Documents/” .. 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:

=ExcelTarget&filename()

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. – dick@plogic.ca

Sub PowerBI()

On Error GoTo PBIErr

Application.ScreenUpdating = False

For Each conn In ThisWorkbook.Connections
conn.Refresh
Next conn

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

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

PBIErr:
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
Else
Set TSheet = Target.Worksheets.Add
End If

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

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

5. 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 PBI.com 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” …

THIS DIALOG IS THE ONE THAT YOU WILL NEVER FIGURE OUT ON YOUR OWN NOR HAVE I EVER FOUND THIS MENTIONED ANYWHERE ON THE INTERNET IN THIS CONTEXT !! 

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.

THE CLICK “APPLY” AND YOU ARE DONE !!

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.

Conclusion

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)
PT.RefreshTable
PT.TableRange1.Copy

with

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

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.

Dick