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

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Business Intelligence, Cloud, Excel, Excel 2013, Office 2013, OLAP, Power BI, Power BI Desktop, Power Query, PowerPivot, Spreadsheets, the Cloud, Uncategorized, VBA. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s