Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Lab-5-4-5-6-7

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
>> One of the other things that we can do with Excel is use OneDrive for Business as a place to store our Excel content. And manage it using things like OneDrive's check-in, check-out, and version control capabilities. That's really useful, particularly if you want to make changes to that Excel workbook over time. And the great thing is we can then connect to that workbook, connect into Power BI, and retain that connection so that any changes to the workbook get automatically picked up by Power BI as well. I'm going to start with an Excel workbook that I've already uploaded to OneDrive for Business. So I've got an Excel file already uploaded to my OneDrive for Business here. Just a simple sales report. And when I open it in OneDrive for Business, you can see I've got a pivot table on one sheet and I've got another sheet here which has got some Power View content in it. So this is actually a workbook that had Power Pivot in the data model set up and Power View sheets as well. Now if I go over to Power BI, I can go and connect to that workbook. All I need to do to get data, I'm going to bring in a file. And again, this time I'm going to choose the OneDrive for Business option. So as I said, connecting to OneDrive for Business like this means that you have a live connection to that file. So either, so whenever anything happens like if you go and add more rows to Excel tables or if you go and update the Power View reports or the data model, those changes will get synchronized to Power BI as well. So I'm going to select the file and hit connect. And I get an option here. I can either import that data into Power BI. And what that's going to do is connect the two together and actually go and create Power BI reports and a power BI data model within Power BI. The data get stay, the data stays in OneDrive and it's just being refreshed on a schedule, as you would do for any other data refresh. The other option on the right hand side is to leave all of that stuff in Excel and just view it. The charts, pivot tables, worksheets, everything that's in that file through Power BI. So let's start with the import option. This will stop processing the file. And as I said, that's going to split up into a data model and into Power BI reports. It's going to be putting out those Power View sheets into reports So you can see here's the dataset and the report is just being loaded. So I've got the same charts and graphs from that Power View sheet copied over here. Now it's worth noticing that if I make any changes to the Excel file, changes to the Power View sheets there, they are override changes in this Power Bi report. I'm going to delete those and go through the process again to talk about the other option. So again, I go to get data, files, OneDrive for Business. And this time let's just do the connection option. So I'm just leaving that content in OneDrive for Business and just surfacing it, just viewing it through Power BI. So now we'll see over on the left hand side, rather than the dataset and report being created, I just see the report. And you can see on the left hand side there's a little Excel icon to show you that this is, something is being managed through Excel. If I choose edit, it'll open in Excel services in edit mode and I can do things like scheduling refresh for this as well. When I hit that I get through to the same schedule refresh screen as I would do for any other dataset within Power BI. But you can see on this workbooks tab, this workbooks tab gives me all of the Excel sheets that are being managed through Power Bi, and the credentials and gateway status et cetera for all of those. I have to do the same thing as I would have done with other sources in terms of telling Power BI what credentials to use. And I'd set up the schedule for the refresh in just the same way. And here, I got the option to say, go and keep my data up to date and then specify the frequency with which it will go and refresh. And what that's actually doing is going over to OneDrive and saying, "Okay, OneDrive, we need you to go and refresh this Excel workbook. We need you to go and do the refresh as if it was just going through OneDrive." But it's Power BI that's managing it. Power BI that's managing the schedule, and Power BI that's storing the credentials and things that the refresh happens with. And again, you'll need the personal gateway set up if you're going to go and connect back to any on-premises sources through that Excel workbook. So that's it. It's as simple as uploading up to OneDrive for Business, uploading your Excel workbooks to OneDrive for Business and then connecting it through Power BI and setting up the date refresh from there.

Video Details

Duration: 4 minutes and 50 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 37
Posted by: csintl on Feb 23, 2016

----- (Please provide translations for these languages: Chinese (Traditional) (chi_hant), English (eng), French (France) (fre_fr), German (ger), Italian (ita), Japanese (jpn), Korean (kor), Portuguese (Brazil) (por_br), Russian (rus), Spanish (spa).)

Caption and Translate

    Sign In/Register for Dotsub to translate this video.