About the talk
Power BI is "the" tool of the moment. But did you know that once you have Power BI Data in the cloud, you can also Analyze it in Excel? I'll show you how to do it and why.
This session will help:
- A business user who wants to have flexibility in analyzing Power BI Datasets in Excel, and learn more how to leverage both together
- An IT professional who wants to provide the best options in his model and governance for the user from the datasets he manages
In a glance, I'll show you:
- How to connect from Excel to the Data Model stored in a Power BI cloud dataset
- Which are the reasons for doing so
- Things you can do in Excel which you cannot do in Power BI
- How to do some amazing things with formulas connected to the cube by using MDX, named ranges and much more!
- How to publish charts and other elements from Excel to Power BI in the Cloud
Pedro is passionate about Leading Teams and turning data, technology, and people together into value. During the last 15 years, Pedro has managed multiple teams in Banking, Supply Chain, and Operations, and also planned and coordinated the construction and migration of thousands of Reports to Power BI, SSRS, and other BI tools. He is the Co-Founder of the Power BI Portugal Meetup group and the Porto Power BI User Group, Invited University Lecturer, and active speaker at events like Power Platform World Tour, SQL Saturday, Porto Data, and local meetups.View the profile
Thank you guys for that having you here. Today. Let me tell you about the oil changes for our station today. And I want to tell you guys a story about about XL about power, bi, and they will Lucien important, and you will understand why. And I want to show you how we can use both part of the eye and XL. Twin alive dating dating sites that you start with in the park meeting either. As I will demonstrate how to do it in Excel and why why do we want to do it in Excel and not in poor guy. Some time to see the Philadelphia to the best of the work? I will show you guys
some ideas and techniques using MDX multi-dimensional Expressions. Well and maybe even turn you guys into a Superuser of, not only accelerate or vip222 together. I hope you enjoyed it. I believe these sessions can be useful. If you are the business user or an Android, if you want to have some flexibility in the way that you work with your Derek, you scheduled for one or the other two, if you want an IT professional that you want to give some good options for your end-user. It's important for you to know the potential of these people connected to
the same data set and you can give them different options in different government for the user. So, let me tell you a bit about me and thanks for the intellectually lazy. I am exacting leader is not easy and the Investment Bank of BBC, one of the largest banks in, in, in Europe. And I work with multiple teams in t.i. And Big Data software development. I have two people from India in my team. One-ways already the Working, Poor to the works, with the Informatica powercenter. You need to get another one work, sweetie. I'm tired of being a big plate of steak with, spark Scala
to play with amazing people from India, exports in, in technology and wonderful human beings. So, I'm really happy for the invitation to be with you guys today here today, and to close the delay. I'm also in casambi reporting and I have a specialization is Flushing from MIT. I work during my career as a business user. I work 10s business, analyst giving Context, you know, of the different needs from different users of data. I replace a Solutions architect as a consultant and I am invited lecture. Also. It's an event in u.s. Like Bridget said, I'm the co-founder of
power bi Portugal, the group apps every month, either. We have local speakers or we have international, get fixed it. Come to order in regular be if you're interested to know more, find out the links in the end, the end, the end, the platform deck. I will check. When I get home to give some lectures about the RTI or platform in the data and I love you. Absolutely believe in this technology is a key differentiator for the Professionals in the IT world. And I think it's the way to go. I strongly believe in it. If I do not
believe in it. I will be giving a lecture at the Tableau conference are the creepy or something like that, but I do Microsoft. To go with our session. I want to tell you first. What is Excel? So popular this will give us some context about the session that we're going to have and to give you away. Let's talk about the story about the invention of the spreadsheet. You guys know about the story, maybe not the fathers of the spreadsheet, Bob Frankston and then two amazing people are really invited.
And I want to show you a little story. The volume of the video is not very loud. So during the video, if you can, please increase a little bit, your volume so that you are able to and then you can turn that down a little bit. One of the first problems that I ran into was, how do you represent values in formulas? Let me show you what I mean. I thought you would. Point somewhere typing some words or numbers point where you want the answer and then pointed the 1st to the 2nd and get the results. The problem was, what should I put in the formula? It
had to be something the computer knew what to put in. And if you looked at the formula, you needed to know where, on the screen that refer to. The first thing I thought was the programmer way of doing it, the first time you poured into somewhere, the computer would ask you to type in a unique name. Became pretty clear pretty fast. That that was going to be too, tedious, the computer. How to automatically make up the name and put it inside. So I thought, why not make it be the order in which you create them. I tried that. Now, you want to tell you to pretty quickly. I saw that if you
had more than a few values, you never remember. On the screen things were then I said, why not, instead of allowing you to put values anywhere or restrict you to a grid. Then when you pointed to a cell to computer could put the row and column in as a name. And if I did it like a map and put ABC across the top and numbers along the side, if you saw a B7 in a formula, you know exactly where it was on the screen and if you had to take the formula yourself, you know what to do? Restricting you to a grid help solve my problem. It also opened
up new capabilities like the ability to have ranges of cells, but it wasn't too restrictive. You could still put an eval you any formula in any cell. And that's the way we do it to this day. Almost 40 years later. Do you guys see any value in any cell? This is really the power of Excellence. This granular control that's quite different from some of the items that we have like a power that you can pinpoint in each cell and you can reference when another pieces with the real part of a cell and gives us already some good about which can be
the use cases when the last four games 18xl. I want to show you guys all. So how was the look of the first spreadsheet to what is the visible calculate the way for a little bit. So is obvious that account going to go public trying to find this bug and they were very interesting and the Apple 3 was the new Apple. Business version of the Apple. They were going to do an apple was holding off going public. That's really weird that you finally. Okay? The shipment the next day, they actually public. The fascinating, right? It was so important this software for the new episode releasing when they are
going public in the in the stock market. It's really a fundamental beastly it in part of the history of Apple computer. Microsoft in Harvard Business School. You will see a plate that in one of the rooms saying. In this show, me 1978, Grand Prix, Queen NBA of 79 conceived. The first spreadsheet program musical will be 40 years later. This is still the basis for what we do today. And so important for many of us today in 2007 and newer and future concert is quite important. Also, the reason it gives us more this way to White Widow
and Son truck getting some use to apply some fun. Like we know today in many of the normal tools that you use in all day today. Then 2010's power people really where you can model that you can combine to create relationships between tables inside Excel. This was born a wedding 2010 inside excel in 2013. Ford Credit is born inside XL, 2, new can do treatment of data and weeding Excel and in 2015, July 24th, Part. VII General availability. And division of property I own. This state is not so different from what we have today.
Introducing power bi by Microsoft power. Bi is a cloud-based business analytics service, enabling anyone to visualize and analyze data with greater speed efficient and understanding. Get started with power, bi n s, just sign up and you're good to go. Thanks for Content packs by popular service providers, like salesforce.com or Google analytics. And in One dashboard, you'll experience a live 360 degree, view of your business, monitor your data in real-time from nearly any device across all major operating systems set up mobile alerts to your phone. When your data changes
and share your reports and dashboards with e. Drag-and-drop features enable you to go deeper with your data and explore it in new ways by asking simple questions. No need to learn a new language with power bi easily clean shape and model your data to create impactful shareable report power. Bi experience your data. Any data anyway, anywhere Sign up for power bi today. So, any data anyway anywhere? I don't know about you guys, but I still have Goosebumps when I when I see this for presentation is really fantastic. So, let's move on.
Take me to move on to the next part. First of all, I want you guys to know is that it's possible to analyze a data set with in Excel. It's exactly the same thing is connecting to an analysis services in Excel. You need just to download and install a plug-in from the party. I am so bored. Come and install it in your local machine. So here we go. Weak in the Fiero and we don't load the package. We don't love you. Sammy side bacon. We install it next next next finish and you're ready to go. And then
when you go to any data sent, you can go to the Same Old Spice. Select analyzing Excel, it will download an XLS file. That will open your mother already ready to be alive analyze an Excel. It's a simple as this weekend to the Y. Now, you know, you can do it. Why do you want to do it in and get it in Excel? From my experience, as an architect and has business analyst in the end user is present. Some people prefer to analyze the dating of people table for my maybe some of them. Have you already had the same
expression a wonderful report, but can I export to excel? So it's very common and sometimes it takes some time for you guys to get into the mindset of doing during the week. 16 party. I ended up using a table. They prefer to do it or is it because they don't have enough flexibility. Also in the report to change it to shave it. If you didn't activate that the user to create on their selves are doing self-service bi, sometimes it just one for tonight. Some of the normal export
to excel of the typing done by then, if you wants to analyze data from multiple party at 8, to 6 at the same time. So too much tomatoes together, you can also do this with this feature noise in Excel. You can collect the workbook to multiple data set of Barbie in the west region. Take advantage of Exile, Grand Haven. See this, like, we saw in the den between video explanation that text at. You can reference any style with any formula and you can reference again, and again, and do computation some dates. If you do it a day with us, to be much more complex
to do it. What is the existing reports? You can connect? You want to consider something mention? You don't have anyone to mention. You can also do it. Let's see a demo about this. What I will do. I will open. And I have a work space that demonstration workspace. What I have a couple of reports yet. I have a report I have to connect it to the week or so we can open if I open one of the report you will see it's just some sale and a with some salespeople and end with a geography they mentioned somebody coming report.
I will go back to work if I don't want to live this in Excel. So let's say I want to click your analyzing so I can do it here or I can do it in the dating site menu. I also have the option to analyze an Excel. It's exactly the same thing. So if you want, if you could download it from from here to have from the Little Ghost and always next to look like we see what I'll do now, is that, okay? And I get an Excel file. I can open it and we'll see what happens.
Kid. It is. My file is already open. And you'll see it opens its native people table. When I click on it. I can see that the title is available for me. For instance. If I open a total sale, I have the value and I'm getting the data from my queue. So if I go to Days Inn menu and I see Quinton connection, you'll see the connection and establish connection to my data scientist or Tina's. So, I have the connection string here Define and I'm connecting to a zore
x l g a s name. It like this connection name. What is interesting to know? This is that I have. Also the power bi report. Shorehaven. And so I am done and I'm sorry that I am just multiplying by two and you see her 116/76 remove change my major in my report. You'll see that the cells will become a half developed. This is just for you to see that today to changes. I will save it. And I will publish again. When I click publish, my username was logged in in party. I will have access to all the work spaces. He is entitled to and I will be able to publish my report
so I can select. It will send it over right before I replace it and we'll go to go. Okay. Got it. You will see here that's on the club into the site. You see that the data set has just been updated this very minute. Now. Let's go back to our XL price. You'll see that the 396, the new refresh data from the club. So we're good to go. And now I can continue my exploration if I want for instance, to use West Gate Estates. And I get the breakdown of sale by each of the state's yet. I have this table
if I want to win a while, another system and the same thing. Can we connect to another device? Should I go? Doing good. Analyze in Excel to I have access to my mother. Are we able to Compton? Let's go and I also have I think my total sales value here and let's see if I have something like a maybe. And maybe I have a country. Oh, the country getting the filters. Let me see if I have. United States. So now, I have my data from this other day, to see, if I copy my people table to my other
work work, and I place it here. I have connections to two different data sets in the party at Club one in the other and I can compare to that. You can be extremely useful in a lot of fields. Caton the step on how to connect and also with the steps. What have we noticed? It already going back to our and pork buns. We can see how you can analyze data in a pool table. For my, you can ultimate some of the the Explorer you can analyze data from multiple Court sometime and take advantage of the XL. Granular referencing what. I'll do the work
already with the two data sets and So if we go to Daytona, you'll see, I am here exactly what we did right now. I have worldwide important than the super started to say. And if I do a double quick in this state, the states. It gets me a table at this table. If I open the properties of this table, it gives me a tax credit and what I can do, parentheses, I can instead change the sex wedding. Employer, if I change it. Try again. Don't you see? I got them fired
employee table, so I can also do this for the other use case that I was showing, which is it, go back out to make some of the normal exports. Do they sell the timing then by the user. So we seen when we covered all these disguises that we can do with working together as a hoe. Will having access to this feature is great. But if you want on the, it side in Mexico to respecting a couple things you need to have it organized because it requires what we call explicit measures. And what I mean by that is if I connect to work me,
go back to the mall to here. This time. I will show you in a second. Maybe it's better than the power bi side. I have a report here. You'll see this with some sign here. This is what we call an implicit measure because it's just the standard assault allegation. That poor guy is assuming and what you want to create, really, and the measure and explicit Meshach and see if I want to hear. I don't think I actually have anyone any measures, but it's the one with the calculator sign that we can Define like a account or the same of a column. It's
something like that. If we can, you find how to increase it? Measures will not. If you don't have any explicit, then you will not be able to do it. Then please 12:35 in Paris. I will not work in Excel. Also, the Heathen tables in fields are not available to report that you are not using all the field that you have in your mother. And then when you use a connect to the Anova in Excel and he can create a custom reports with other kids that you don't have in your car. Again, part. In any point, you
want to remove this field? You cannot because you have used using, it is wiser to have a bad experience for them. So if you allow this picture of allowing use this feature of finalizing XL, you have to think about the continuation, principal to This is exactly what we had when we go back to his final example. I want to show you too. So if I go to party, I can I open. I think it's in the gallery and you see this beautiful world, the movie part that you have a lot of ppis and everything. But this report on you see
Treetops and you don't see actually the model that you have in your report insult when you actually go to analyze it in Excel. And if I go there and go to the club before you go here. Stick in just a second. So I can show it to here. Instead of in the direction. They sell, you seen your mother behind the scenes behind the curtain. That is a whole lot of things a person to have a table, which is called you. Like, in December, you have a table feedback airport, codes and guests and able to
imagine that user connecting this in Excel. And seeing all these tables in all these papers. It will be very confusing for him out. Till the reporting party. I really need to, we need to think of your professional if we activate then I was in Excel before. Mother is really clean and prepare for the new principal. So this is why it's important to think about this. OK, Google Also, is it people tables are really amazing, a great tools for analytics. Maybe the worldwide most used to for analytics.
First excel in the people tables. This is the feature in Excel. How can you build something like this with people table? What I mean is that you have 2 or 3 years in three years, but you in the prophet, in the quantities, respond to show it in the last year of the yard. Last three measures. You will have $9, * 380 people table. It's the same thing. So, how can we do this for my specific with that, you were? So let's go back to our demo. What we can do. Is we pick one of the people tables we have here? Otoscope
a spectacle, just create a new one and I still have the same connection I can do is go to the NYC tap. Go to / 2. And click converter farmers. American tortoise, eat create me an expression. And now this expression here. It's converted me to take a function. This state is a function and the final value value function referencing, our connection, and the combination of three elements, three, crewmembers the states, and the destruction of our killer about this. Now. I can manipulate the statement as I please do, if I want to change
disco member to, Texas. I'm just right here. And I put you will see. The value is change. This is connected to online and its Dynamic and you can really play with your data like this. So it's really interesting. Don't know. How come you will solve the challenges that we've seen in the previous examples of what I want to do. Is I want to populate this table here. So I'll go back to here. Replace the cell instead of Texas article. This text. I will quick and go. This expression expression. I can do the same thing. Goes for the
other states if I wanted, or if I do the same thing. Just woke up. You actually the text. and what I will do, I will do a dynamic reference of lipsticks and And respects. And What's a now? I can drag-and-drop and you'll see it's just referencing. I have these all these crew members and what I will do is exactly the same thing for the year. I have one here for the year. Covid-19. Tina s. What is the effect of this? Remember, I will do the same thing in each of them. I have already here, the formula for profit and quantity.
Call Michael Jackson. And I remember expression should be this one. And I fix the column and row and the expression. Best Buy. It gets data from my queue and now I can copy paste this formula. Populate with the arrow, maybe like me, just pick it up. And I do, and everything is dynamic and very quickly. I can do, I fix table. Imagine if I want to do a comparison. How is my sales from one year to the other? I don't have those restrictions table. I can do. How is
this one compared to the previous year? -1 And I can do like a percentage. Minus 64%, something like this. You see I can do all this analytics. I can go now and do conditional. Formatting data bar, something like this. So a lot of things I can do. So you see I have complete control over my data at the Grand word bible. So going back to our Do you are able to fix this and solve this problem for you to know about this? Understand the language of the cubes, which is, you have to function, fairly basic
points, to call this expression, to call David from Kew, you to start typing, your to Valley, you reference your connection. And then you can put as many members as you want. 1 2, 3, or as many members was two sets as you want. It's summerall's, you can cross any number of members in Sex, You can only use it when measured princess total sales or profit but not too too. Of course, people throw an arrow and a maximum of one element at each other or subcategory, but not. So you need to pick one by each of the group.
You can also manipulate MDX has just showed it to reference. Dynamically the Texas, Pennsylvania fair that I can reference, Texas and put that in the in the middle of expression. And we see that with this comforter formulas. We give Independence for the word cubes. We can just create any type of excel cells. We can go any day this week from out, you getting a lot of power to Vari grind with reports like princess. An index of together. So we can do is using this and I will give you a quick on that. What we can do is I want to total sales
by a group of salespeople. I have already here local member 123. What I want to do is create the group said, they just called me. So Johnson, so what I will do. Jeff bubier. Oklahoma connection, we're waiting for her. And the expression will be just is the most of these people and exceptional being instant job. Getting paper. And you will see, you know that if I, I can also start typing your problem from the beginning. I can connect like you also. Really? I can call my connection worldwide in quarters. You know, I can
hold one of the members for instance, I want from myself. And knock myself. I can control my measure. I want to have my photocell. Straight up. This is my valley of the total steps, another element, which is going to be Easter Johnson. Miss Cook said you will see. This is actually the total sales of the year. This one, this one. Let me see if I have 15 in 2016. I have some sales. If I can say all this history, people, you'll see that the veil is will change. Now. It's only the sale of these
three people together, and you can do the same group as you please. I can do the same thing for me to sleep here and existing. Referencing this one bit by itself into cups. And when I go pay my phone, or it will work. The others to how can I do that? I'm basically going not to use, but not to refer any so people. I'm guessing the total alcohol, six people and then I just do. Play next. Phil and I can do this in a very easy way. Are you going to breakfast yet?
Otherwise, it will always be total sales. So if I work This is the way that we can, we can do. Let's go back to our present for time. This is not too many use just for you guys to know that. He's also lost. And you can convert the date for math with his formula text and you can change into a format that you can manipulate the Aztecs. So we come to the, we've seen already this weekend. Now, if you want to feed you this week or something mansions that are not available.
We saw the sweetest kill people and we also seen it like me show you, which can be interesting to know it's a city existing reports. Let me show you this one here. What I mean by that? Is Mission weather report here from marketing? They have to keep your eyes from Google analytics or something like that. And now you are changing your sales data from another report that the next sale at 112 York, Martin data in a 72-in in PS and stuff like that, that you have another. So how you can do that because you cannot do any type of rapport with
all day. You want to stay today to hide from the other stores. It's better to use it. You keep your report. Well, connection was like in Parker and then you do you recall expression? Look up pills. Nope. If I, if I, if I do exactly the same thing. My report is connected to the cube with the exactly the same format that I had. Before you say to cancel the party. I data. And the other thing is completely linked to whatever it was connected before is working just fine. Maybe this is not so simple.
Before the time of the yogurt. How could we make it more? Simple? I will tell you the stink-eye Deep for a couple of them to make it really simple to use and just getting into today. Once in Excel. What is the base? And I use it with the combination of named ranges. This is the solution I propose. Let's see what we can do is a creative bass and the club members from the dimensions. If I use more regular, just like the one we seen from Texas Pennsylvania, and I
give it a name, I can go to this still here and I can call you sell, Texas. What is means when I quickly go to Texas, which is by itself become a member and I called, he's my son, bring my give me the total referencing will call this expression, 2 * 12. I will crawl 2016 is I think it's the name that I want to give it. And I can give the names for the other. Let's be real last. When we clear history. I can give it the name like that. I can call this Easter history. He's third wheel. So after doing this, what can I do now?
Let's try. I want to have the sales of 2015. Covelli. We're fighting Parkers. Year 2015, in 2015. And also get it. So, which works? Northeast of 2015 in Texas. Make some sales. Year 2015. August 29th. Enter and I guess the Basin from my queue just like as I'm typing whatever. I want with this combination and work just fine for you. So, considerations about this, any cell can be assigned. The name range. There are some rules about it. You can only contain letters numbers.? In the
scores in space. My numbers. That's why I give the year 2015 2016 year 2016. So you can see, if you're a real use case, for the same people that needed information about the sale. Going to start. You could type Care number of the store. Can you get data from your Requiem? The sales or anything else? You want to get yourself? You're getting from the cube in an Excel sheet. We talked to you that I having to do anything like that. It's just a simple example of getting data in seconds. How to disconnect with a board game, axis architecture. Well,
as you know, already before that, you could analyze whether in the browser we party. I thought you can also not what I'm showing you today. You can also analyze it in Excel to and and you can also I get some things in Excel and published some work, books or two or also, I don't think it's the device, practicing. I usually don't use it a lot. So, diagram by the rain, and I love this one. It's really cool. As a reference for the architect. Well, according to the Treaty of your carpet analytics, self-service bi and also to have a
TPI action-driven ability can be Even huge organizations like Maersk only recently moved into to CSV to excel in Toledo. Sells, go to work. Or do I keep your mother's cleaning guys? Looking good. Like we show me the examples about the naming convention. What it is to come. And if you can deliver everything you want to your stakeholders, Isn't it nice that you have some options to, to do? Maybe they already have options to work with the date 31 in the form at the one,
where this option of the Excel and you don't need to create a custom report for them, or really mess up with your mother. It's great to have options for customers to be satisfied. And leave you here. Also their contacts, please, my email, if you want to get in, touch my LinkedIn to my block meeting with Dr. Net and get in touch with me, and I'm really pleased to be here with all of you together in India and to close this session. Thanks again for the invite.
Buy this talk
Buy this video
Our other topics
With ConferenceCast.tv, you get access to our library of the world's best conference talks.