cancel
Showing results for 
Search instead for 
Did you mean: 

Find articles, guides, information and community news

Most Recent
ArchitectMadhan
Super User
Super User

Now, you can make the Chat Bot more effective in handling queries from Customer by using Power Automate Flow.

Read more...

codevenkat
Super User
Super User

In Power Automate one of the common scenarios was to extract Look up Field Text which will be a nightmare for the people who are new to Power Automate about how to extract details of lookup field. As an example, on update of Contact Table, Account Details will be extracted.

Read more...

codevenkat
Super User
Super User

In Power Automate, during certain scenarios we must traverse all the records in excel file table and based on few conditions content in excel to be updated. List Rows action present under Excel Online(Business) Connector in power automate can be used. As an example scenario of updating eligibility of Employees based on Age explained here.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we must join few items which received in the form of strings. As a scenario, response of the user was taken to show this function behavior.

Read more...

codevenkat
Super User
Super User

One of the common scenario was to update data in Excel Spread sheets . To achieve this functionality automatically, we can use an action- Update a row into a Table in Power Automate under connector Excel Online (Business). As an example, after Sending emails to contacts, Employee details will be updated with sent Date time stamp in a separate column.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we must validate values and proceed further if result is true, in these scenarios If function is used. As a scenario, response of the user was taken to show this function behavior.

Read more...

codevenkat
Super User
Super User

One common manual work was to send emails to list of users, where this can be achieved by using Power Automate. As an example, sending emails through excel sheet from OneDrive considered to explain this.

Read more...

codevenkat
Super User
Super User

Most of the common usage of storing data in Excel Spread sheets and a common scenario was to add data into Spread Sheet. We can use an action Add A row into a Table in Power Automate under connector Excel Online (Business). As an example, Employee details will be passed to Spread sheet through Power Automate.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we must validate two values and proceed further if result is true, in these scenarios Equals function is used. As a scenario, name of the person and current years values were taken to show this function behaviour.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we must validate two expressions and proceed further if both the expressions are true, in these scenarios and function is used. As an scenario, name of the person and lucky number values were taken to show and function behavior.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we must repeat certain steps until a condition met. In these scenarios we make use of Do until function as an example counter value will be incremented till it become 3.

Read more...

codevenkat
Super User
Super User

In Power Automate, for doing validations or Function will be used. As an example, an String variable and a number is used to do this validation.

Read more...

codevenkat
Super User
Super User

In Power Automate, for doing validations Not Function will be used. As an example, an String variable used to work with Not function.

Read more...

codevenkat
Super User
Super User

In Power Automate, generic arithmetic operations like Less Than will be used to validate provided integer values. As an example, an integer variable used to work with Less than function.

Read more...

codevenkat
Super User
Super User

In Power Automate, generic arithmetic operations like Greater will be used to validate provided integer values. As an example, an integer variable used to work with Greater function.

Read more...

codevenkat
Super User
Super User

In Power Automate, generic arithmetic operations like GreaterOrEqual will be used to compare between values. As an example, Speed Limit with integer value considered to compare with other integer values.

Read more...

abm
Super User
Super User

Recently Microsoft announced Graph API HTTP Request action step. Good news is its not a premium connector. There are lots of potential for this.

 

In this blog I will explain how to create a table and read an Excel sheet dynamically?

 

My scenario is drag and drop an Excel file (No table) in One Drive. Power Automate will trigger and read the Excel contents.

 

Lets start building the flow.

 

Here I am using my flow trigger as When a file (Excel) is created under OneDrive.

 

 

 

Next we need to capture the Excel file identifier which is needed for subsequent Graph API steps below. Graph API URL to get the identifier is:

 

https://graph.microsoft.com/v1.0/me/drive/root/children?$select=name,id&$filter=startswith(name, ‘{triggerOutputs()?[‘headers/x-ms-file-name-encoded’]}’)

 

image.png

 

Next we need to capture the body value (See below Content Value mapping : body(‘Send_an_HTTP_request’)?[‘value’]) of the response above and collect the identifier and name. For this I am going to add a Parse JSON step and pass the body value (body(‘Send_an_HTTP_request’)?[‘value’]) of response body of the previous step. See below.

 

image.png

 

Above Parse JSON Schema is as follows:

{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“@@odata.etag”: {
“type”: “string”
},
“id”: {
“type”: “string”
},
“name”: {
“type”: “string”
}
},
“required”: [
“@@odata.etag”,
“id”,
“name”
]
}
}

 

To avoid the loop I have used the following expressions to find the Excel file name and Id.

body(‘Parse_JSON’)?[0][‘name’]

body(‘Parse_JSON’)?[0][‘id’]

 

Next we need to find the used range values of Excel worksheet. This is used for creating the table with specified range.

 

To find the used range using Graph API is as follows:

https://graph.microsoft.com/v1.0/me/drive/items/outputs(‘Excel_File_Id’)/workbook/worksheets(‘Sheet1’)/usedRange(valuesOnly=true)

 

image.png

 

Next capture the body response and look for the address property to get the value range (eg:Sheet1!A1:B7). We are interested in the cell values only. So we need to use the last() and split() expressions to extract the cell range. See below.

 

image.png

 

Above expression is as follows:

last(split(body(‘Send_an_HTTP_request_4’)?[‘address’],’!’))

 

Next we are ready to create table.

 

Graph API to create table is:

https://graph.microsoft.com/v1.0/me/drive/items/outputs(‘Excel_File_Id’)/workbook/worksheets(‘Sheet1’)/tables/add

 

Under the body we need to pass the range value earlier we found and set whether the excel sheet has header or not.

 

image.png

 

Above step will create a table under the worksheet. Above under the URI I have hardcoded Sheet1 which is the default sheet name for my worksheet.

 

Next I am using the Parse JSON step to get the table id by passing the body from previous step. Using the filename and table id we can read the Excel sheet contents.

 

image.png

 

Above Parse JSON Schema is as follows:

{
“type”: “object”,
“properties”: {
“@@odata.context”: {
“type”: “string”
},
“@@odata.type”: {
“type”: “string”
},
“@@odata.id”: {
“type”: “string”
},
“style”: {
“type”: “string”
},
“name”: {
“type”: “string”
},
“showFilterButton”: {
“type”: “boolean”
},
“id”: {
“type”: “string”
},
“highlightLastColumn”: {
“type”: “boolean”
},
“highlightFirstColumn”: {
“type”: “boolean”
},
“legacyId”: {
“type”: “string”
},
“showBandedColumns”: {
“type”: “boolean”
},
“showBandedRows”: {
“type”: “boolean”
},
“showHeaders”: {
“type”: “boolean”
},
“showTotals”: {
“type”: “boolean”
}
}
}

 

Now we have built our flow and below is the overall flow looks like.

 

image.png

 

References:

https://docs.microsoft.com/en-us/graph/use-the-api

 

https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

 

Hope this is helpful and thanks for reading my post.

 

codevenkat
Super User
Super User

In Power Automate, at times we must work on JSON with String Values as input. We can use JSON function in power automate to convert string values to JSON and perform related operations. As an example, string values separated with comma and also xml string values are used.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we must work on strings which have XML values. We can use XML function in power automate to convert string value to XML and perform related operations. As an example, notification xml string is used here to send notification to required person.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we must work on fraction values, float function is used to achieve this. As an example, in order to send discounts notifications to customers during festival season we make use of this fraction function to provide price of a product after discount.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to take decision based on Boolean value, it could be string or Boolean value. As an example, Power outage variable with true value is considered for demonstration and then Bool function is used for condition validation.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to merge 2 different strings into an Array we can use Create Array Function so that we can get easily desired result. As an example, let us see how to get an array of emails with 2 strings as well as process to merge two different arrays of questions and answers into a single array.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to compare values Min Function is used so that we can get easily desired result. As an example, let us see how to get Minimum age of 2 persons as well as minimum value from a given array of integers with the help of Min

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to compare values Max Function is used so that we can get easily desired result. As an example, let us see how to get Maximum age of 2 persons as well as maximum value from a given array of integers with the help of Max function.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to perform Addition arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate total age of  2 persons with the help of Addition function.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to perform Subtraction arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate warranty period that is available in years for Generator with the help of Subtraction function.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to perform multiplication arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate number of minutes in a Day with the help of multiplication function.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to perform division arithmetic operations so that we can get easily desired result. As an example, let us see how to calculate price of a candy for quick reference from a bundle cost with the use of div function.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to perform Modular arithmetic operations so that we can get desired result, as an example we can see number of customers waiting for tables based on the customers present in a hotel , so that customers will get information about the number of customers waiting , so that they can plan accordingly.

Read more...

codevenkat
Super User
Super User

In Power Automate, at times we need to have array with integers starting from the specified index for doing different operations in design of online exams or quizzes to provide random questions this function would be useful. 

Read more...

Helpful resources

Meet Our Blog Authors
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Blog site: https://ganeshsanapblogs.wordpress.com/ MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/
Join Blog
Interested in blogging for the community? Let us know.