Hi Bob,

Session 8.04 of the Power BI course covers publishing Excel files to Power BI. You need to use the 'Export' option and if your Excel file isn't saved to OneDrive then you need a Personal Gateway (session 7.02) to refresh the connection to the Excel file and get updates, or you can re-export the Excel file to Power BI to update it, which probably isn't ideal.

If I were you I'd recreate the queries and model in Power BI rather than have it linked to the original Excel file. Shouldn't be too difficult to copy and paste the queries and measures.

Mynda

Thanks for providing such a great service, Mynda

Really appreciated and sounds like perfect advice ... 

I was obviously getting in a pickle trying to link between Excel and Power BI Desktop, and not fully taking on board the last few lessons of your course (clearly I was excited to be completing my third course) ...!

Would I get similar results from linking original Excel data to Power BI Desktop, setting up queries and measures on Desktop version, and then publishing to Power BI Service? 

All my files are stored on OneDrive, but both my OneDrive and Excel accounts are based on a personal sign up, that is different from my corporate PowerBI Service one.  If I switch my Excel sign up to the PowerBI Service one to enable Export workbook then I lose access to the Excel files (I think).

This all gets very confusing when you are starting off - probably a subject that would benefit from the excellent simplifying graphics you have deployed elsewhere on PowerBI course ...?

Sorry if one question leads to another - I feel like a toddler always asking questions!

Bob 

Hi Bob,

You can get files from a different OneDrive account to that of your Power BI account. You need to use the From Web connector and pass it the https://... SharePoint file path to get file from OneDrive. This video explains an easy way to get the correct file path.

Mynda

Thanks Mynda

I will check this out.

Hi Mynda

I am still badly stuck on this (despite, maybe because of, several weeks' holiday).

In essence, I want to take several Excel datasets, clean them, subject them to a few queries, define relationships, add a few DAX formulae, create some data visualisations and publish them to web.  Over time, I want to be able to update the Excel files or replace them with newer versions, so that updated data visualisations can be published to the web.  

My understanding (!) is that I should use PBI Desktop for much of the heavy lifting (in preference to this being embedded within Excel files), before publishing the report to PBI Service (ie the web-based version of PBI), and finally publishing to the Web.

If the above is right, then how do I go about exporting Excel files to PBI Desktop? 

Importing them does not seem to allow the possibility of updating the underlying data. 

Using Publish Export from Excel looks like it will necessarily link up with the PBI Service only, but this would give me two problems:

(a) exporting a file from my personal One Drive account to PBI Service (which uses a different corporate account - sadly, your 19 July reply did not enlighten me)

(b) PBI Service appears very limited, in that it only deals with clean formatted tables, does not support defining relationships between data tables, nor manual creation of tables eg to define sort order of categories.

Does para 2 correctly describe the process I should aiming for and, if so, how do I ensure that data visualisations are updated readily?

Thanks  

Bob

Hi Bob,

Is your source data in .xslx or .csv or .txt format? I've heard there is a bug with automatically refreshing data stored in .xlsx on OneDrive, but .csv or .txt files will automatically refresh.

So, if your data is .xslx format then store it on your PC or local network, get it with Power BI Desktop, then create your reports before publishing to the Power BI service for sharing. You will need to install the Gateway (session 7.02) to refresh the connection between the source data and the Power BI service.

However, if your data is in .csv or .txt (or you can easily change the file format from .xslx to one of those) then you can store the data on OneDrive. Get it with Power BI desktop, create the reports then publish to the Power BI service and refresh will happen automatically once every hour.

More info on importing Excel data stored on OneDrive and SharePoint into Power BI here.

Mynda