Pass Your Microsoft Power Platform PL-900 Exam Easy!

Microsoft PL-900 Microsoft Power Platform Fundamentals exam dumps vce, practice test questions, study guide & video training course to study and pass quickly and easily. Microsoft PL-900 Microsoft Power Platform Fundamentals exam dumps & practice test questions and answers. You need avanset vce exam simulator in order to study the Microsoft Power Platform PL-900 certification exam dumps & Microsoft Power Platform PL-900 practice test questions in vce format.

Power BI Desktop - Managing Data

2. Menus in Power Query Editor

In this video, we'll look at the menu options and see what they do in broad categories. We won't be clicking on each and every function and seeing how it works. That's not the point of this course. We will just be looking in broad categories. First of all, all we've got is the file menu, and the file has Close, Apply, and Close Close, meaning do not do any of the changes we've just made. Reject all the changes. Applying means saving all the changes but keeping this Power Query Editor window open. You can also find it here in the menu. New Source This gets us to our dialogue box to get more data. And you can also do this by writing and clicking on Queries Queries Pins, going to NewQuery, and selecting whatever query you want. Recent Sources Enter Data does not advise you to do so. I recommend that you have the data in a spreadsheet or elsewhere. This is a preview of 1000 rows or so, so you can refresh the preview if the data changes. You can choose what columns to keep or remove, and you can choose what rows to keep or remove. Those are more powerful. If you're using sorts, these are the keeping and removing roles. So if you sort by date, descending to the most recent on the top, and then remove the first row, then you'll probably be removing the most recent day. Obviously, there may have been several roles on the most recent day, but you get the idea. So you can use the filters to choose which columns to keep or remove, as well as which rows to keep. Bear in mind that these are permanent changes to the steps as long as this one is here. So if I just look at one particular date It's added a new step. I have to click on the X to get rid of that step if I want to go back to the previous data. So it's not like Excel, where you can filter and unfilter and that will be fine. There is no unfilter button. Now incidentally, when applying a new step, make sure you're on the latest step; otherwise, usually you will get an error message. So, if I go here and remove or move the top row, it says inserting an intermediate step. The one between these two can affect subsequent steps, so make sure you're on the later step. Transformer. So these are about splitting columns. If you've got multiple values in one column, change the data type, which you can also do here. You can see the list of data types you've got using the first rows of a header. These are the promoted headers that they find in order to get from here to there. We'll have a look at the combined section in a later video. And then we got AI—artificial intelligence—insights, which are powered by Azure or Azure-based insights. So we'll look at this course about AI builders, but these aren't the only ones. These are in a separate AI section. Transform and Add column are fairly similar. Transform takes an existing column and changes it. Add column creates a new column based on your selection. So I could transform this date and time column by saying I just want the year, and that creates a number. Instead I could add an additional column, and if I do that, that will give me a separate column. So it comes down to whether you want the existing data to change or whether you want new data. So we've got lots of functions, textfunctions, formatting splitting, extracting, we've got numberfunctions such as multiplying, adding and soforth, rounding, and we've got date timefunctions, extracting, month, quarters, weeks, days, etc. We also have pivoting, which you will find in Excel. You've got pivot tables, but we've also got UN pivoting. So Transform takes an existing column and changes it. The Add column adds a new column, and there are a few additional functions in the Add column as well. For instance, you could create an index column, which is just a column starting at one and going all the way through. In View, we've got whether you want to view layout; you've got looking at the data preview; we'll have a look at this section in a later video; and you've got a few more advanced item tools. If you want to diagnose where things are loading slowly or if a particular step is taking a while, you can do so there, and then there are some help resources. So these are the menus in the PowerQuery Editor homes—your most popular things. Transform takes an existing column and changes it. Add Column adds a new column, maybe based on an existing column. Then there's View, which we'll look at in the following video.

3. 38. Clean and transform data

In this video, I'll be looking at how to clean and transform data. But firstly, you need to know that there is a problem with your data. Let's go back to our table, HP Admins, and you can see that we have got some Knowles. Knowles means "empty," nothing there." So how many of these would contain nulls? Well, you could vote on it, I suppose, and find out. Or if you go to View and Data Preview, you can take a look at the column quality. So, if I click on this, you can see that7% of the values are empty, 0% has an error. How can you have an error? Well, let's say I wanted to change the region name to a date where it's a string that doesn't make any sense. So that will correct 100% errors, as you can see. And 93% of them are not empty, not errors; they call them valid. Of course, valid is in the behold of them. might not be really valid; it's just that they're not empty and not errors. So, you get this for all of your columns by using column quality. Next, let's have a look at the different types of values we've got. and we can see this using the column distribution. So again, for all of the different columns, we can see how many distinct and how many unique values there are. So area for instance, we've got three distinct values,midlands, north and Northwest region, we've got six distinctvalues and none of them are unique. However, in averaging prices, every single value is unique. Remember, this is a preview in the top 1000 rows. So we're saying there are 1000 distinct and 1000 unique things. So in this twelve-month percentage change, there are 929 different values, of which 928 are not duplicated. Same here in the sales value: there are 894 different values, and there are 794 that only occur once, which means that there are 100 values that occur multiple times. So that is the column distribution: distinct and unique. And then we have a look at the column profile, and you notice nothing has actually happened. also clicked on it, but now you need to click on a particular column, and now it gives you some statistics about it. So it gives you things we've had before: error and empty, distinct and unique, the count, the first value and the last value, and then a value distribution. So, if I click on Sales volume, you cansee for instance, 2443 has been used three times,3311 has been used twice and so forth. This should allow you to get a feel of the data. Now we've got these threequality, distribution and profile. How do we know what's what? So we can see whether the data needs to be cleaned. Well, quality. I've got my questions about whether there's an error or not. These are questionable values. Quality starts with Q, empty, error, or question. Start with Q U, and you'll see that quality provides it for all of the columns. Distribution starts with D, as does Distinct, which gives you it for all of the columns. A column profile gives you both of these qualities and distributions. So you get plenty of data with thecolumn Profile p column profile plenty of data. However, because there's plenty of data, it can only show it in one column. So hopefully this helps you remember qualities where you were unsure whether it was empty or an error. Distribution gives you something distinct or unique, and column profile gives you plenty. Everything that I've just talked about, but only one column. Now, if I go back to January, you can see that we've got a problem. We've got a leading space, so I'm going to transform this. So if I go into the text column and I go into format, I can trim it. So that I can do lower case, upper case, and so forth, I'm going to trim it. And now we have got all of the subtypes without any of these hidden spaces. So you won't need to know more than this, really, that you can trim, that you can split, that you can add to numbers or multiply that sort of thing, or that you can extract dates. So, for a first look at your data, consider the column quality with errors and the empty column distribution for distinct and nondistinct. And Column Profile can only show you one column at a time. And then if you want to transform them, go to the transform section. And you can see we've transformed this one bygetting rid of leading spaces and then spaces.

4. 37. Combine multiple data sources

In this video, we're going to look at how you can combine multiple data sources. And for this, we go to the home tab, and there are three ways to combine. The third way combining files is used for whenyou have a data source of a folder. So if you've got a folder and you go to "File Folder," then the computer will open up, say, all of the spreadsheets that are in that folder, and then combine them. So quite often, this is done behind the scenes. So you connect to a folder, and then you say load or transform, and the computer does that for you. So that's more of an automatic process. So that's combining files. However, for your queries, there are two ways of merging queries and appending queries. So if I want to merge a query, let's say I've got this subtype, which I've also got in this category, and I want to have this column imported into this query. Well, first of all, you can see the headers haven't been imported, so I'm going to click on "Use first row headers," and now we've got headers. I can do this by merging queries. So if I click on merge queries, I can either create a brand new query or merge into this one. I'm going to merge into this one. So what I'm going to do is I'm going totake the subtype of January and I'm going to addinto that the category with the subtype there. So I'm going to just leave all the other options as is and I'm going to click OK, and now we've got a new table to the right, and I can extract this table, click on this little icon that's appeared, and say, "Is this an e-book?" And now we've got no yes. You will not be required to know any of the specifics about how to actually do that. That's more of the DA 100 certification. However, you should be aware that if you want to combine multiple data sources in order to obtain additional columns, you should use the emerge queries function. Now suppose I wanted to add additional rows at the bottom. Well, that's the append query. So here we've got a January table and we'vegot a February table and we want to justput the February table at the bottom of January. I can do this using the appendix. So again, I'm going to append to this. I'll append as a new query and soI get to choose what my tables are. If I've got more than two tables, I can merge them all together as well, but I'm just going to stick to two tables in this particular instance. So I'm going to click OK. Now we've got a new query coming up which I'mgoing to rename, which I'm going to call Expenses. And you can see that it has got the January data and the February data all included, and you could add in March, April, and so forth. So if you wanted to add additional rows by joining the row of one query with the row of another query, then that is appending. So, merge, add, columns, append, advertisements, and roles. So this is how you can combine multiple data sources.

5. 33. Describe the Power BI Desktop Reports, Data, and Model tabs

So now we have our expense query. So I'm going to click on Close and Apply, and I'm going to create a new visualisation based on this. so it is now just loading. And here we have our new queries. Now let's say I don't want to see January and February where there's no problem. I can click Dotted and hide it. There are other ways to hide it as well. Here's one particular way. Now, I'm going to create a new visualization, and I'm going to have, as it says, a bar chart. Well, I'm going to create two bar charts, let's say. So in this first one, I want the date and the measurement to run across. and you can tell it's a measure with the sigma value going up. Now, let's just format that. I'm going to do this in terms of presentation dollars. So I'm just going to click on it, then click on Column Tools and click "dollars." just a presentation. But I can't see my individual bar chart items. All I can see is one big year, 2017. Well, this is because 2017 is a part of a date hierarchy. Year, quarter, month, day And what it's doing is showing me the top of the hierarchy. So I can either drill down, so I can click on this button, for instance, to get down to quarter, then to month, and then today, or if I just wanted the date and not the hierarchy, then I can click on this dropdown and say instead of a date hierarchy, I just want the date. Now, you can see there's quite a difference there, at least in this default stage, because we've now got January 29 and February 5. There's no data for January 29. We got January 30. So what I'm going to do is put my date hierarchy back in place and then just drill down. So in my second visualization, I want, again, I'll puta bar chart and I will have out in termsof the values, but I want the category. Is this an ebook on the axis? To be honest, this is probably better as a pie chart. So let's convert this into a pie chart. and you can see that the computer has recognised a relationship between expenses and category. It might not do that, however. So how can we see what the relationships are between various queries? Well, let's have a look. On this left-hand side, we have got three different tabs, including the report tab. This is where you create your visualizations. So it's the tab that we've been using all the time. It's got filters, it's got visualizations, it's got fields, and it's got a huge amount of menu items. The second tab is a data tab. So this shows you, for any particular table, the data. And you can do filters and so forth, which will just filter the data that you're seeing here but won't actually change the visualizations. It doesn't go through to the report type. It's just a temporary filter. So much data! This allows you to see the data, and it's not a preview. You can see we got 1584 rows. The third tab is model. And what this does is show you all of your queries and any relationships between them. So you can see that the computer automatically created a relationship between category and expense in terms of the subtype. So here we can see the relationship instantly. Hidden tables can also be found in this tab and the Data tab. So if you're wondering how to unhide hidden tables, that's how you can do it. So if this relationship didn't exist, I would delete it, and then the computer would have no information on how to actually create this visualization. Is this an eBook? No. Is this an eBook? Yes. I've got no idea. So instead, in your z, it's 50/50; you really have no idea whether anything is an ebook or not. So instead of dragging from one table to the next, I have my relationship and my analysis. So the Report tab is there to create your visualizations. The Data tab is there to look at your data, and the Model tab is there to relate different queries together using relationships.

6. 43. Design data layout and mapping

In this video, I'd like to talk about designing data layout and mapping. And of course, we've done a lot of this. If I go back into an existing query by clicking on the dot, dot, dot next to it and going to edit query, you've seen that the Power Query editor allows us to, for example, remove particular columns. So if I don't want columns to be included in my dataset, they won't be. I could rename columns. So maybe I don't want this to be called a date. I want this to be called something like a date of expense. That can be done as well. We have also seen that columns can be transformed. We've seen this in general terms, like, for instance, when we trimmed a text column. So we can do something like split columns, format them, extract them, get the first few characters or the last few characters, or for numbers, we can add, multiply, subtract, divide, and a few other things as well. So this again allows you to customise your data layout. Now you can add columns as well. So suppose I wanted to extract, for some reason, the second letter of this word. What I could do is extract the range. So I start at the index number. I think it might be index number one. Yes, because this is zero based.The first character is character number zero. There are just a few quirks about extracting text, for instance, but you don't need to worry about any of that. But this allows you to design your data layout exactly how you want. And as I said previously, we can rename. So maybe this is the second letter that you need. So it could be that you have an item with the code SA 12345 and you want to extract the SA because the SA means something as opposed to an SB 12345. So you could extract the data there. You can now do similar things if I simply remove that from the Power Bi desktop. So if I go to the data, this doesn't have to be done in the data, it can be done in the report, but it's more visual here. I could get that second letter out, hereby adding what's called a calculated column. So, after selecting the desired column, I would click on the "tools" column. So this is my second character, and it is equal to mid in the subtype comma two, comma one. so similar to what we have seen in Excel. Notice that just like in Excel, the first character is character number one. So we've got the Power Query Editor, where the first character is character zero. Here, the first character is character one. There are two different languages. It's called M in the POWER query. Here it is called DAX. DAX. So adding a custom column gives you an extra column for each individual row. There is something else you can do, and that is called a measure. So a measure does not give you an answer per individual row; it gives you it for the totality of the data or whatever you're looking at. Suppose we were here and we were looking at January 14th, then it would give you an answer for January 14. So let's take an example, and I'm going to have a measure. So what I'm going to do is call it out with sales tax, and it is going to be the sum X. So you can see similar to Excel, butstarting to go out into more complicated functions. It's using the expenses table. And I'm saying if, well, let's have the amount multiplied by if the subtype is equal to "e-book," then we don't have any sales tax, otherwise we add 10%. So this is the calculated measure here. So I have a different icon here, no extra columns because I was able to calculate a column. But we can use this measure, say, here. So if I add the out with sales tax into our values, here is our out, and here is our out with sales tax. Or, if it's an ebook, there's nothing extra added, but if it's not an ebook, we'll add 10%. So you don't need to know how to actually create any of these calculated columns or calculated measures, at least not for the PL 900 certification. What you do need to know is that they exist. Calculated columns will get you an extra column, an extra answer per row, and calculated measures; they are a form of aggregation but use formulas to calculate the aggregation. And you can always add additional columns to the Power Query by going to Add Column. Just one more thing about the data layout, and it's something that I've mentioned before, and that is the hierarchies. We can have hierarchies of things like dates. So we have a date hierarchy: year, quarter, month, day. But that's not the only hierarchy we can have. We could have a hierarchy, for instance, of country, county, or state; city; street; or district; who knows? And so you can rearrange these areas, these fields, into different hierarchies. Assume I wanted a hierarchy of region names followed by area names. Well, what I can do is create a new hierarchy. Just click on the dots (dot, dot, dot) on the right-hand side to say "new hierarchy." And here we have a new region name hierarchy. And I'm going to add area to the region name hierarchy. So if I wanted to, I could create a new table and drag in the region name hierarchy. I actually think it's the other way around. I think areas go first. All I'm going to do is I'm going to drag it above. So let's say I wanted to create a pie chart or a column chart or any other kind of chart, and I would have the hierarchy in the axis and the average price in the values. So now, I've got, as a hierarchy, the North, Northwest, and Midlands at the top level. And then further down, I've got West Midlands, West Yorkshire, and so forth. And I can go up and down the hierarchy like this. This just shows the region name. For this button, the go to nextlevel and this one combines them. So that shows area and region names. Now, when you create your own hierarchies, you probably don't want the original fields to be there. so we can hide them. I can click on the field next to them and go into hiding. So I can hide the area, I can hide the region name, and I'll just be left with the hierarchy. So hierarchies are automatically used with dates. You can switch that off, but generally, they are automatically used with dates. But you can also use them with other things where you have a top level, go down to a middle level, and go down to lower levels. So this is something else that you need to consider when considering data layout and mapping.

Go to testing centre with ease on our mind when you use Microsoft Power Platform PL-900 vce exam dumps, practice test questions and answers. Microsoft PL-900 Microsoft Power Platform Fundamentals certification practice test questions and answers, study guide, exam dumps and video training course in vce format to help you study with ease. Prepare with confidence and study using Microsoft Power Platform PL-900 exam dumps & practice test questions and answers vce from ExamCollection.