PL-100 Microsoft Power Platform App Maker – Importing and exporting data

1. 43. Load or create data records for testing and development

Now part of testing or development is to be able to create your tables and then load records into it. So in this video, we’re going to have a look at the various tools that you can load and indeed save data. So first of all, you need to get into your tables. That’s either data table or solution and the relevant solution, and then tables. So let’s go into our Spence table and into data. So here you can see the data we’ve got. And we previously said you can change the views, and indeed, you can create views. So you can see what data we got. So here we got my view, for instance, or you’ve got all columns, or maybe have a different set of columns, like custom columns, for instance. So that’s looking at the data that’s there. But what about if you want to edit it? Well, at the top we have got data. So we’ve got various things. First of all, we can edit the data in Excel. So here is the data in Excel.

So now it has been successfully, it says opened in Excel. Really, it’s been created as an Excel document. You need to open it. And once you open it, you can see I have got this protected view. I will enable the editing. And on the right hand side, we have got an add in. This add in enables the viewing and editing of your data. You notice that at the moment, there is no data. So I’m going to click sign in and I’m going to sign in with my account. And once it’s now signed in, there’s your data connector retrieving metadata. And it says that some fields cannot be found, like stage ID or the may be private. But here I can now add things. So I’ve got all of the columns. Despite the fact that this view doesn’t have all of the columns, it has downloaded all of the columns. It’s independent of the view. And so I’m going to add another item. So let’s make this 9876.

I don’t have to put in everything. I’m going to put in a different account name. So you can see I’ve got a list of the account names. It’s been quite helpful. I can put in a date, a description. So this has been added, an expense value and anything else that I would wish to add. Then I can publish it. So publish all data source changes. So it has been published. If I refresh this now, refresh data. There we have our new entry. Nine, eight, seven, $623 and so forth. Otherwise export the data so that exports it as a zipped CSV file, comma separated values. I can get data.

So I can get data from a variety of sources. And we’ll be looking at this, which is based on the data flaws here. It’s called power query. We’d be looking at that in the next section when we look at Power bi. Additionally, I can get data from Excel, which is actually not just Excel. It could be an Excel spreadsheet, but it could also be a CSV file. So you upload the file, and then you can map the columns to the columns in your table. So in this video, we’ve had a look at how we can get data and upload new data. So one good way. Edit the data in Excel, and then you’ll be able to have a live presentation of the data in Excel, and you can adjust it with this fairly handy add in. So I can add in a new account number from a list, for instance, or I can select a date.

So it would be quite handy if you created your own table, and then when it was blank, you export it. You edit the data in Excel rather than using the export data or the get data functions, and then you’ve got this live connection. However, if you want a static connection, then you can export the data in a CSV file, or you can get the data from Excel or from lots of different other sources.

2. 64. Merge data from a data source into a Microsoft Excel template

In this video we’re going to have a look at how we can merge data from a data source into a Microsoft Excel template. So let’s go to our model driven app. So I’m just going to play the accounts and expenses and I’m going to go into expenses. So here we have a view and we can change the view here. But suppose none of these views are exactly what you want to be able to download. So I can get a view and I can export it to Excel and I can open it up and there is my data. But maybe I want something a bit more personalized to me. So we have people setting up all these views, but I want my own personal download and that is called an Excel template. So what I need to do is download a template, alter it how I wish, and then upload it again into the system. And that then becomes a personal Excel template. So let’s download this so I can select the entity.

So I’m going to select expense and I can select a starting view. Well, this view is fairly good, so I want to edit the columns first. I don’t necessarily want all of the columns which are here, or maybe I want additional columns. So I’m going to add deed of expense as a column. So I’ll download that. So file downloaded successfully modify and upload to create a template. I’ll open that up. You don’t actually have to modify it if you don’t want to, and enable editing. Now, this isn’t the entirety of the data, this might just be a few records. Now let’s modify it. Let’s suppose I want a column which says 10% of the expense value, or 20%. Maybe I’ve got value added tax and I want to calculate what value added tax on top of that would be. So 20% of expense value and that is equal to the expense value multiplied by 20%.

Now, make sure you use things like expense value, which I just did, by clicking equals and then clicking on the expense value in that row. Don’t say for instance, equals G two times. You must use things like expense value. So it is a field which, as you can see, has a lot of hard brackets. But you don’t need to worry about any of that. You just need to type equals and click and the computer will do it for you. Right, so there’s my modified template. So what I’m going to do is I’m going to close it now and I’m going to upload it. So I’m still in my model driven app, I’m in one of the forms and we’re going to upload the template. So I’m going to choose this expense and open and upload. And now it is installed for me to use as a personal template. So let’s change what I’m seeing. Let’s go on to active expenses. So we had three rows, we’ve now got many more.

So I’m going to now go to this template and you can see it’s got the file name. So you may want to rename the file name and you can download it, or you can open it in Excel online. And you can see here we have the 20% of expense value which has now been populated. So the computer has worked it out. So this is great because I didn’t need to add a new column of 20% of expense value. So me, as not a system admin, I’m able to create these personal templates and then say, I want to do this. I want to have the difference between these two fields. I want to do some other sort of calculation. I’m not limited to just the calculations that the system administrator has put in. And of course, I can open it in Excel as well. So enable editing. And there we go.

Now, this is called a personal Excel template. So in other words, it’s just for me. If, however, I wanted users more generally to have access to this template, then I’ll need to go to the Classic Interface. So to do that, I go to Star and Advanced Settings and I go now to Templates on the left hand side. So I want to work with a document template and I want a new and here’s my Excel template and it’s saying, okay, I want expense and a particular view. So I could have added additional columns like maybe Date of Expense and Currency. Let’s put both of those in. So I will download this file again, open it up. So there it is. I want the expense value doubled this time.

So let’s save this, but save it with a different name. So this is my Excel template. Close that upload. It from in here in the Classic interface. So browse to find that file upload. And there it is. So if I go into Templates and Document Templates, you can see now we have got a template. If I refresh this screen, then we have these Excel templates. So non personal one everybody can see together with my personal templates. And I can edit my personal templates here in terms of being able to see what I’ve got and then being able if I click on the checkmark to the left hand side of editing any of the details or downloading it or deleting it, I can also deactivate it from here as well.

If I want, however, to go to my non personal ones, then I can do that as well by going to Settings and Templates and Document Templates. And if I check that again, I can activate, I can deactivate, I can delete, I can do all these things. So in this video, we’ve had a look at how to merge data from a data source here into an Excel template. So we can open up the model driven app, go to a relevant form where we’ve got lots of data. And then we can go to Excel template and from there we can open in Excel online or we can download it. So here you can see I have got the expense value, double the currency and the date of expense all here.

3. 64. Merge data from a data source into a Microsoft Word template

In the previous video we created Excel templates which allow us to export a huge number of records in an Excel spreadsheet. But we could customize it as we wished. Well, suppose we weren’t looking at a huge number but just one. So I’m going to just click on a single reference. So I’m going to open this, record this raw. So what we’ve got here are Word templates and Word templates allow us to create a mail merge from this one particular record. So let’s download a template and we have a bit of a different dialog box. First of all we’ve got what entity, what table is it? Well it’s the expense table. Then do we want any tables which are related to this where the expense table is on the one side and the other table on the end side, the menu side. Alternatively, do we want to download anything which is the other way? So in this case we have got many expenses for the one account.

So I’m going to download the account as well. And then are there any to many relationships? Now you might wonder why it has all of these different settings. Well that’s how it was in the classic version and I think they just wanted to continue it like that. You could all have all of these in one big dialog box. So having selected the data you want to download then you click download and it doesn’t actually download the data, it downloads the metadata, it downloads the information about the things. So let’s open this up and I’m going to enable editing and I’m going to save this right now as my personal Word template. So we need to do a few alterations because it looks completely blank.

First of all I need to be able to see the developer menu. So I can do that by clicking on this drop down and go to more commands and then customize ribbon and scroll down until we’ve got the developer menu and I’m going to check that and click OK. Secondly I go to the developer menu and then to the XML mapping pane. So if I go now to a different XML part have a look for the table, the entity that you have extracted. So here we can see the Word expense.

You also notice it starts with U-R-N so I’ll click on that and expand this. And here we can see all of the fields within this expense table. So all of the custom fields are probably grouped together because they’ve got this prefix and then underscore if I scroll down you can also see that we have got the account table as well. So let’s create our mail merge, our document template.

So I’m going to say the account is and I’m going to go down to the account name right and click and not left and clicking Insert Content Control, plain text and then I click outside of it and press the dot the period. Now if I click inside it you can see that there is some kind of field there. So next I’m going to say the expense is and I’m going to go up to expense value that’s there, right? And click Insert Content Control, plain text and was incurred on and we got the date of expense so insert that as well.

Now, what you might find helpful if you’re doing this is to actually just draft the text and then go back over it and go okay, the expenses and I need an expense field. So you might find that easier. Right? I’m going to now save that so close and go back and go into Word templates and I’m going to upload a template and it’s going to be that my personal Word template.

So click open and upload. And now I’ve created a personal template. You can see it here. So let’s go into something different. Let’s go into this one. So we have got $345, no date of expense. I think I’ll choose one with a date of expense. There we go. So let’s go and download my personal Word template for this particular file. So it exports to Word, I open it up and then we get a Word document which says the account is fabricam incorporated. The expense was $1,234.

Notice it’s formatted and was occurred on the 16 June. So hopefully you can see that this might save you a lot of time if you’ve got a lot of data and you want individual Word documents that can be done just at a click of a mouse. Now this is a personal Word document. Again, personal to me. So if I just rename this as my budget Word template and again if I go into the templates area of the classic so to get there I go to the gear and then advanced settings, I can go to Document Templates and I can download a Word template.

So what I do is I select the entity and click Select Entity. And then I select what my tables are, my other entities I want, click Download Template, do exactly the same thing, modify it as I want, and then when I finished, I can upload the template browse to upload a file exactly the same way as a digital Excel. So there’s my Word template I prepared earlier.

Click upload, there it is. And now if I’m in my model driven app form, I can go to Word templates and there is my global Word template as well as my personal Word template. So Word templates, they’re useful for when you want to extract the details of one particular expense into a Word document. So you can download them from the form saying Download Template. You then open up the developer tab, you then open up the XML mapping pane and then you can right and click on what you want doing include and insert Content Control as plain text.