Microsoft 365 Community Power Apps, 11 May 2023

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: How to automate the process to convert the rows of excel sheets into the rows of Dataverse

Description

In this 13-minute developer-focused demo, Abdul Wahab opens with a demo of a final solution, then lines out the prerequisite tools/access requirements and steps through uploading an xls to OneDrive and the importation of data into a Power Apps table and subsequent movement into Dataverse. Uses Import document and 2 supporting Power Automate flows. See the expressions used in Power Automate along with possible errors (learning opportunities) and explanations.

This PnP Community demo is taken from the Microsoft 365 & Power Platform Development Community call recorded on May 11, 2023.

Demo Presenter
• Abdul Wahab (Dynamics Technology Systems)

Community Call Conversation
• aka.ms/May11-Demo2

Supporting materials
• Documentation - Sync your Excel data source with Dataverse using a dataflow | https://learn.microsoft.com/power-query/dataflows/sync-excel-cds-dataflow
• Documentation - Import or export data from Dataverse | https://learn.microsoft.com/power-apps/maker/data-platform/data-platform-import-export

Learn more
• Microsoft 365 Unified Sample gallery - https://aka.ms/community/samples
• Microsoft 365 Platform Community in YouTube - https://aka.ms/community/videos
• Microsoft 365 Platform Community - http://aka.ms/community/home

A

B

I have seven plus years of experience in Microsoft, Dynamics, CRM and Power Platform I'm working in a different roles with different organizations and I'm actively participating in different Microsoft communities. For any queries uh you can reach out to me through this email address. So this is my topic: how to automate the process of converting the rows of excel sheets into the rows of database using Power Platform.

B

So these are the prerequisites which we write for in this automation, so Excel sheet OneDrive. So these both components we have, through this personal account and and the fourth one, is the power labs, and we have this through the account provided by Our IT department. So, let's jump to the demo. So this is my Excel sheet and I'm going to upload this so This OneDrive in this document folder and it will convert to these rows of this import records table. So, let's import, let's upload this one.

B

So here we go here, it is uploaded. So this let me try to open that one uh quickly. I have made all the bookmarks okay, so it is going to load it's okay. So here is my solution, and here is the flow okay. This flow will require two to three minutes to run in the meanwhile, uh we can see the technical stuffs, so let me quickly share my right.

B

Okay, so these components are used to build this automation, solution, table power, automatic model, one app and connection reference, and then I'm going to share the technical details from my environment and okay. This is my account uh created by my it department, and this is my solution. Let me okay, this is my solution and all these require all these components make this automation. So, let's see uh them one by one. So, first of all, I created this table uh which is responsible uh for saving these all Excel records. So here here is a choice.

B

Column, and here is the date time column and here is the name column and it has the whole number column. So I have these four columns in Excel sheet and I. Have these four columns in my table so for the visual visualization I created one model domain app here? Is this application? Here's a model application and here I just add it to the navigations. So that we can see the records uh in the more user-friendly environment, so this is my application and and after that, I created these three power automates.

B

So, let's see one by one. So here is my first automate, which is uh responsible to convert the Excel sheet raws into the database, Ros, so I'm going to share what is done in this power. Automate I use this trigger, and here, as you can see here, uh this is my document folder, so I need to oh I need to log in uh to my personal account here. This is my: uh it Department account. uh I use power of power.

B

In other words, you can say: I use, powerapps from my department account, and we do not I do not have OneDrive on in this account. So I need a personal account for that purpose, so that I I created personal account for the purpose and then I use this connection reference and this this is connecting to my personal account, and this is yeah here. You can see I'm literally I'm referencing documents folder, so whenever a file uploaded to the documents folder.

B

So this this power automated, is responsible to trigger and then after that, I need this file identifier in in the blue processes, so I save it to the variable and then I need this file path. As after when this file is successfully uh parsed, then I need to move this file to this purchase folder. So for that, for that purpose, I need this folder path. Okay, so this is the folder path. Okay- and this is the fold folder Source, folder path and I can see. This is this. Is the path of this documents? Folder, okay?

B

So here, okay, here my screen so through this power out portable component I can pass my all records. So, as you can see here, I need to identify the table so in in my Excel. Yes, there is a table. You can see here, there's a table one. So every time I I want to parse my xlos, so I need to Define one table here, so here I'm using table one Okay so until we're using table one. So one thing here interesting is: if you need to pass that time uh column, you need to pass you.

B

If, if you uh didn't select this one, so it it will going to share some numbers, so I think I recommended it. You use this format date time format. So if you do not worry about the conversion of this say time, column, okay, so now, then the next thing is, you can apply to each component here, I created one more power of automatic, so I think as a professional developer. Once you need something, um you think you need one something common, so you need a function yeah. My this power automate is working as a function.

B

So let me try to show the details of this power automate. Okay, so here's the power automate. So the thing is in my table as this Choice column, and we do not need this label in power automate in SLC. We have these labels, but to set the value of this option set, we need its value, so I need I. I write this power automate to perform this action.

B

As you can see here, I'm I'm passing at this level to this power automate here you can see so it receive the type label here in the in the in the string format and then I use this value to return uh to the parent power automate. So this value is right. Now this is integer the right now. This is a zero here. So I use this switch statement. So one this power automate receive business from the parent power automat. Then it will going to return to Value one uh similarly for family, similarly for social.

B

So let me try to share the details of this Choice column, um import records. It is loading okay, and this is my table. These are columns and this is the choice.

B

So, as you can see here, so business uh Label returns one value so all of the all of these I configured in this my power in this power automate. Okay, so once it pass the value and then after that it is going to return the response in this value variable, as you can see here and I I, have this response in my parent power automatic okay, so here I'm using this component at a Euro through here, you can see. I can give the import records name here and I give the table lamp.

B

So the interesting thing is: let me try to show you from so when you are configuring, this power automate. You do not have this column here, so let me try to show you so you do not have anything name here. So what you need you need to write. Something also add these details uh to my in my slides, so you need to use creation, so you need to write this expression items and then apply to each and then then the name of the column. So here I wrote all these formulas here.

B

So when you first run when you first after your first run here, it is after your first run. uh This will move in this format so in the same manner, I use the same expression for the date time. Format, I use the same expression for the whole number, but here you can see the choice. Column will be filled using this with the response of this column. So, as you can see here, let me try to show you from here. Okay, I think I need to do it more maximize so that all people can see.

B

Clearly, okay, okay, when my power automate receives zero, it means the value the label it is going to pass. Is it is not there in my options at column, so I'm going to save this value as the null and after and this. If this is not the case, then I'm going to save the value written by the power automate. So here it is so this is how I'm creating records okay. So the third and the last step is I, move this file from this document folder to picture document.

B

Once all the records has been passed, then the next thing is I need to move this document from this folder to the pictures folder. Okay, for that purpose, I am also using the professional developer mindset and I use this power automate as a function and I'm going to share what the steps why this power table is doing so. Okay, okay, here I'm going to open my new solution screen.

B

Yes- and this is my cloudflow, um this is the flow- is responsible to move files. So there's something uh there's one thing is interesting, so as you can see, I'm sending the file and identifier of the source file and the destination folder path. So this my power automate- and here you can see here it is- it is taking some time. Okay.

B

So here you can see here with the help of this body, just on a schema I'm receiving this from the power automate and I use this variable to basically when we are working with the Excel sheets, so sometimes it is locked. So uh so, when I try to move this from the source for path to the resolution path, then sometimes we need the locking mechanism here. So whenever we need an error here, so I set the delay function here with this function.

B

It is wait for the one minute and then it is try again to move this folder antenna. Unless a DOT move the folder, then it is, uh it is in the this profile Loop and once it is finished, it successfully finish its job. Then I make this to false, and then my this power that's successfully finished. So let me try to show you the records converted. Okay, the file will be uploaded. So, as you can see here, uh we all of these records are converted now- and here we can see here so here.

B

I can show you one record. Okay, so here you can see, the name. Column is uh set to the name column and the choice column is set to the choice column. So all all are set very well here you can see. So the last thing is what we have done with this movement. So let me try to show you and this file is still not moved. So, let's see so here you can see, it is still running. It sometimes takes 12 minutes, sometimes takes 22 minutes.

B

So after some time it will move file from this folder to destination folder. So, let's jump to the sum some errors which I faced during this development, so if I root apply to each Institute of instead of apply to it, so I receive this error, so you may face when we're developing when you are configuring, this power table. So you may face this error, so the resolution is, you need to check the component of the apply to which what the name you are. You are using with this component and I space.

B

This error, when I am passing null to my automat, which is responsible to take the label of the option, set and return the value and, and then the next thing is I, face this error when I give a value to them to my integer column and then I face this error when I setting the string Institute of date time so I I root. All these errors, which I face during my development, so I think that that's all from myself.

A

Abdul awesome: this is really really great stuff for, for those that are looking to yeah Crowd Goes filed, awesome stuff. Thank you so much for sharing there's a lot to pack into this. You did a really fine job really really appreciate it. So thank you.