Different ways to work with Microsoft Excel in Power BI (2023)

Aug 13, 2023

Different ways to work with Microsoft Excel in Power BI (2023)

Using Excel? Of course, you are! How can you take advantage of Power BI? Adam shows you the different ways you can leverage Excel with Power BI to get the most out of it!

📢 Become a member: https://guyinacu.be/membership \r
\r
\r
\r
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.\r
\r
🎓 Guy in a Cube courses: https://guyinacu.be/courses\r
\r
\r
LET’S CONNECT!\r
*******************\r
\r
— http://twitter.com/guyinacube\r
— http://twitter.com/awsaxton\r
— http://twitter.com/patrickdba\r
— http://www.facebook.com/guyinacube\r
— https://www.instagram.com/guyinacube/\r
— https://guyinacube.com\r
\r
Gear\r
🛠 Check out my Tools page - https://guyinacube.com/tools/\r
\r
#PowerBI #Excel #GuyInACube

Content

0.15 -> - Yooo!

0.983 -> Adam Saxton with Guy in a Cube

2.4 -> and in this video, we're gonna look at the different ways

5.04 -> that we can work with Excel.

6.57 -> There's so many ways

7.89 -> and how do you know what to do and and where it goes?

11.43 -> Let's find out.

12.263 -> (upbeat music)

17.82 -> Finding us for the first time?

18.81 -> Be sure to hit that subscribe button to stay up to date

20.97 -> with all the videos from both Patrick and this guy.

24.78 -> Chances are you're working with Excel

26.58 -> because most people work with Excel.

28.14 -> Now you've heard of this Power BI thing

30.18 -> and you want to figure out

31.35 -> what's the best way to work with Excel with Power BI.

35.61 -> And I'm here to tell you, there's a lot of options.

38.25 -> You're gonna have to figure out, do I want to stay in Excel

40.41 -> or do I just want work with my data inside of Power BI

43.05 -> and leave it in Power BI?

44.34 -> And so let me walk you through the different options

46.47 -> that you have depending on which option you want to go with.

49.05 -> All right, now enough of all this talking.

50.34 -> You know how we like to do it here on Guy in a Cube.

51.96 -> Let's do what?

52.95 -> Let's head over to my computer.

54.15 -> You may be working

55.44 -> with a couple different options inside of Excel.

58.11 -> So you could be using Power Pivot, Power Query

60.57 -> or you could just have straight data inside of Excel.

62.73 -> So let's see what our options are.

64.17 -> So if I go to File, let's start off with this Import option.

67.98 -> And down below you'll see Import Power Query, Power Pivot,

70.77 -> Power View.

71.603 -> So this is the option if you have an Excel workbook,

74.88 -> you're using Power Query

76.38 -> and/or an actual Power Pivot data model inside of Excel.

80.49 -> And by the way, let me know in the comments

82.2 -> if you're actually using Power Pivot inside of Excel.

84.39 -> Been a while since I've heard people reference it.

86.82 -> And you'll see

87.653 -> that I've got a couple different options here.

88.92 -> So this actually has a model inside of my worksheet.

92.94 -> Let me go ahead and hit Open.

94.17 -> And it's gonna say it wants

95.58 -> to import Excel workbook content.

97.83 -> It's not gonna work directly with the Excel workspace

100.47 -> but it's gonna keep that data

101.64 -> and move it all into Power BI Desktop.

104.07 -> So let's go ahead and hit Start

105.21 -> and we'll see a couple things here.

106.38 -> One, it brought over the Power Query queries.

109.41 -> It actually brought over the data model tables

111.39 -> that were inside of Power Pivot.

112.68 -> There were no KPI measures

114.21 -> and there were no Power View sheets.

117.24 -> Also, let me know in the comments if you actually know

119.34 -> what Power View is and you've actually used it.

121.89 -> So this is all there.

123.09 -> If we hit Close, we can actually see our data here.

125.76 -> If we go to our data model,

127.38 -> we can see that we've got relationships

129.54 -> and we've got our data here.

130.62 -> So that took the Power Pivot model

132.9 -> and it migrated it to Power BI Desktop.

135.3 -> So now everything's in Power BI Desktop,

137.52 -> which means we're disconnected from that Excel worksheet.

140.76 -> Power BI now is its own thing over in this corner.

143.97 -> And so anything you do in Excel

146.01 -> doesn't translate to the Power BI Desktop file.

148.26 -> All right, cool.

149.093 -> But what if I just have a regular Excel workbook?

151.17 -> We got a couple options.

152.13 -> One, you can hit this Import Data from Excel

154.26 -> or we could go to get Data Excel workbook

156.42 -> and go through the normal options.

158.37 -> So I can go through Power Query, I can transform it

160.71 -> then I can bring it in my data model.

162.12 -> This is connected to the Excel workbook.

164.25 -> So if you go to refresh your data,

165.81 -> it's gonna go pull the latest data

167.34 -> from that Excel workbook itself.

169.407 -> And so you may need a gateway to do that

171.3 -> if it's sitting on-premises.

172.59 -> If it's in the cloud like OneDrive for Business

174.51 -> or SharePoint Online, then it should just be able to connect

176.85 -> to that directly and pull that data.

178.89 -> So in this option, we're not going through that import step.

181.89 -> We're just getting data.

183 -> We're treating Excel as a data source in this case.

185.58 -> So that's Power BI Desktop.

186.87 -> But what are our options for getting our Excel files working

190.68 -> with the Power BI service?

192.09 -> So if I'm in an actual Excel workbook,

194.67 -> I've got some options here as well.

196.02 -> So if I go to File,

196.98 -> there's gonna be an option here that says publish.

199.29 -> And from here I can publish to Power BI.

201.21 -> You're gonna have two options, uploading your workbook

203.64 -> to Power BI.

204.473 -> So if you wanna use Excel online inside of Power BI,

206.85 -> this is the option you're going to use.

208.74 -> The other option export workbook data to Power BI

211.89 -> is actually gonna create a data set inside of Power BI.

214.98 -> And so this will again be

216.9 -> that disconnected experience with Excel.

219.18 -> You can also choose which workspace you want to go to.

221.67 -> So if I choose this, I hit Upload,

223.29 -> I will see the workbook listed

224.94 -> in my workspace inside of Power BI.

227.58 -> One thing to note here is if your Excel file is sitting

230.64 -> in OneDrive for Business or SharePoint Online,

233.34 -> it won't work going to a defined workspace.

237.03 -> It will only go to your My Workspace.

240 -> So if you want it to go to a specific workspace,

242.01 -> you're gonna have to have this file sitting on your drive.

244.47 -> This is specific

245.303 -> to the publish option inside of Excel itself.

248.16 -> So if we come to Power BI service, this has changed up

250.59 -> a little bit from what you may remember a while back.

253.2 -> And so there's a couple options now.

254.76 -> We have new, and then we have upload.

257.49 -> The new option is gonna be, I wanna create a dataset

260.88 -> or I want to create some artifact in Power BI.

263.3 -> So this could be a report,

264.63 -> it could be a dataset, it could be a data.

266.31 -> You know those are artifacts inside of Power BI.

268.44 -> And if we go this option with Excel, it's gonna try

271.26 -> and create a dataset off of that Excel workbook.

274.5 -> It'll create a new artifact.

275.73 -> It won't know anything about the Excel file after that

278.22 -> but you're gonna get your starting point

279.6 -> with a Power BI data set.

280.86 -> So if we go to Upload a file, we'll come into here,

283.2 -> we'll say Excel, go into my Excel files

285.57 -> and here we can pick which options we want.

287.76 -> So let me just go ahead and pick this option.

289.41 -> We'll say Import, bam.

291.21 -> There's our file.

292.043 -> We can see our actual items that are here.

294.78 -> So this is our Power BI data set at this point.

297.33 -> And we can see that the data set is here.

299.01 -> So this is not bound to that Excel workbook anymore.

301.56 -> Now let's go back and see what happens

304.23 -> if we do this with that Excel workbook

306.9 -> that has the Power Pivot model inside of it.

309.33 -> This file, it doesn't have any actual worksheets in it.

311.91 -> The worksheets are blank.

312.93 -> All it's got is that Power Pivot model.

315.24 -> And it's got some Power Query options in it as well.

317.49 -> And we can see here the same thing.

318.78 -> This is actually the defined model

320.34 -> that was in the Power Pivot.

321.36 -> So it's cleaned up a little bit

323.01 -> and we can see all of these items inside.

325.62 -> So it actually brought that Power Pivot model

329.01 -> and created a data set off of that.

330.96 -> Again, we're disconnected from that Excel workbook

333.6 -> and we can see these items here.

334.89 -> Now, what does upload do?

336.12 -> Let's go to OneDrive for Business.

337.65 -> And I wanna see, okay, my Excel workbook too.

340.44 -> Let's go and upload this.

341.88 -> You'll see it actually created the workbook.

344.07 -> So if I click on here, this is the Excel online experience.

347.76 -> And so if I just pointed to an Excel workbook,

349.83 -> I still want to keep that Excel online experience.

354.09 -> I don't want it to convert to a Power BI data set.

356.52 -> And we can see here, here are the files here.

357.9 -> I've got all my worksheet tabs, everything looks great.

360.27 -> And this was actually generated from Power Query.

362.28 -> Now, one thing I noticed here is

364.5 -> that if we do go and pull this model2,

367.95 -> so this actually has the worksheets plus the model.

370.44 -> If it has Power Query or Power Pivot items inside

373.83 -> and I try and do this Excel online experience,

376.08 -> this actually will fail.

377.43 -> And I'm not sure if that's gonna be fixed at some point

380.97 -> or if this is expected behavior, but it will actually fail.

383.82 -> Also, if I tried to do that published to Power BI

387.24 -> and I choose something that has the Power Pivot model

390.27 -> or Power Query options inside of it,

392.46 -> that actually fails as well.

393.99 -> So if you do have that Power Pivot model,

395.52 -> you're gonna need to go

397.2 -> through actually importing that into a Power BI dataset.

401.28 -> Let me know when the comments also,

402.45 -> are you using Excel online experience inside

405.09 -> of the Power BI service?

406.83 -> I don't really come across that with folks mentioning it

409.26 -> or maybe you don't even know that that was even an option.

411.78 -> One last thing I wanna talk about here again,

413.52 -> we can bring a full circle.

414.6 -> So we imported this AdventureWorks model

416.79 -> which was a Power Pivot model in Excel.

418.59 -> It's disconnected from Excel, but now we can come back

421.29 -> and then we can say Analyze in Excel.

423.66 -> That'll create the file.

424.65 -> We can open the Excel in the web.

426.63 -> Yep. And now this is connected to my Power BI dataset.

431.07 -> And so we've gone full circle.

432.3 -> We can create pivot tables

433.47 -> with that dataset that originally came from Excel.

435.99 -> It's kind of like inception, but it all works, right?

438.3 -> Or we could go back into Excel, go to get data

441.15 -> and pull it from a Power BI data set directly, right?

444.27 -> And you can work with Excel in those options.

446.19 -> So there are a lot of different ways you can work with Excel

448.56 -> to get the most out of Power BI.

450.3 -> Was this video helpful for you?

451.41 -> Let me know when the comments,

452.79 -> which options do you end up using?

454.38 -> How do you work with Excel?

455.73 -> I wanna know in the comments.

457.38 -> If you like this video,

458.213 -> be sure to hit that big thumbs up button,

459.84 -> smash it if you so desire.

461.49 -> If it's your first time here, hit that subscribe button.

463.92 -> And as always, from both Patrick and myself,

466.41 -> thank you so much for watching.

467.88 -> Keep being awesome and we'll see you in the next video.

Source: https://www.youtube.com/watch?v=Pdbc_Q7pnD8