Power Automate: Export To Excel With Dynamic Table & Columns Creation

Hello Friends,

Welcome back with another post on Power Automate. Today, we will learn how create a table in excel dynamically along with the columns where number of columns are not predefined. The export data to excel. The outcome of this post will be-

1. 
2. 

Let's start-

1. First of all, we need a SharePoint list. Below is the list, I had created. It has 8 columns.
2. 
3. Now, the objective is that I wish to export any combination of columns-
1. ID, Title, First Name, Last Name 
2. ID, First Name, Date Of Joining, Salary, Designation
3. ID, Full Name (First Name + Last Name), Designation
4. As we can see in above examples, the number of column as well as the columns itself are not fixed. Till now, we were creating a defined template which is having a table with columns and using it to export the data. This approach will not work here.
5. Microsoft had given solution for this problem as well. Let's begin-
6. Create a document library in Site Assets or the Documents library. Let say "ExcelFiles".
1. 
7. Now create a blank excel file and save it with name "EmployeesData.xlsx".
1. 
8. Upload this file in "ExcelFiles" folder.
1. 
9. Now, open the Power Automate maker portal and create a new Instant cloud flow with flow name as "POC-ExportExcelDynamic" and trigger condition "PowerApps".
1. 
10. Add a variable that will interact with PowerApps to get the column names.
1. 
11. Save the flow and keep saving the flow at regular interval during the flow writing.
12.  Now, to create the table in excel dynamically, we should know the number of columns that are going to be created and based upon the count, the Excel cell address. As we know, that excel works upon cell address. Columns are defined as A, B, C, .... while rows are defined as 1, 2, 3, ...
13. So, for the first row the first cell address is A1, second cell address is B1 and so on.
14. Similarly, for the second row the first cell address is A2, second cell address is B2 and so on.
15. Similarly, table is also dependent upon cells. For a 3 column table, the header address will be A1 -> C1. For a 4 column table, it will be A1 -> D1.
16. However, there is no direct method to get the respective address of cell using the count. Here, we have to apply some trick. We will use an excel file to get the series of the address based upon count. Excel has provided "ADDRESS" function. Here is the excel.
1. 
17. We have to create a JSON and will use the same. Here is the JSON.

1. [
     {
       "ColumnNo": 1,
       "ColumnAddress": "A1"
     },
     {
       "ColumnNo": 2,
       "ColumnAddress": "B1"
     },
     {
       "ColumnNo": 3,
       "ColumnAddress": "C1"
     },
     {
       "ColumnNo": 4,
       "ColumnAddress": "D1"
     },
     {
       "ColumnNo": 5,
       "ColumnAddress": "E1"
     },
     {
       "ColumnNo": 6,
       "ColumnAddress": "F1"
     },
     {
       "ColumnNo": 7,
       "ColumnAddress": "G1"
     },
     {
       "ColumnNo": 8,
       "ColumnAddress": "H1"
     },
     {
       "ColumnNo": 9,
       "ColumnAddress": "I1"
     },
     {
       "ColumnNo": 10,
       "ColumnAddress": "J1"
     },
     {
       "ColumnNo": 11,
       "ColumnAddress": "K1"
     },
     {
       "ColumnNo": 12,
       "ColumnAddress": "L1"
     },
     {
       "ColumnNo": 13,
       "ColumnAddress": "M1"
     },
     {
       "ColumnNo": 14,
       "ColumnAddress": "N1"
     },
     {
       "ColumnNo": 15,
       "ColumnAddress": "O1"
     },
     {
       "ColumnNo": 16,
       "ColumnAddress": "P1"
     },
     {
       "ColumnNo": 17,
       "ColumnAddress": "Q1"
     },
     {
       "ColumnNo": 18,
       "ColumnAddress": "R1"
     },
     {
       "ColumnNo": 19,
       "ColumnAddress": "S1"
     },
     {
       "ColumnNo": 20,
       "ColumnAddress": "T1"
     },
     {
       "ColumnNo": 21,
       "ColumnAddress": "U1"
     },
     {
       "ColumnNo": 22,
       "ColumnAddress": "V1"
     },
     {
       "ColumnNo": 23,
       "ColumnAddress": "W1"
     },
     {
       "ColumnNo": 24,
       "ColumnAddress": "X1"
     },
     {
       "ColumnNo": 25,
       "ColumnAddress": "Y1"
     },
     {
       "ColumnNo": 26,
       "ColumnAddress": "Z1"
     },
     {
       "ColumnNo": 27,
       "ColumnAddress": "AA1"
     },
     {
       "ColumnNo": 28,
       "ColumnAddress": "AB1"
     }
   ]
   

18. You may create as many as column numbers you want. Ideally, keep it "Total Number of Columns in list + 10" so that in case, if one or 2 columns got added later on in the list, it will not impact your flow.
19. Now, add an action called Parse JSON in flow and paste this JSON in Content as well as use this JSN to create schema "Generate from sample".
1. 
2. 
20. Now, we have the columns list that were passed through PowerApps and we have the mapping structure of Column Number vs Cell Address.
21. Add another action called "Filter array".
22. Here we will filter the JSON to find the corresponding cell address row (Column Address property in JSON) using the count of columns passed through PowerApps.
1. 
23. It will give us the item which is having ColumnNo property equals to the total number of columns received from PowerApps.
24. Now, we will fetch the ColumnAddress property from this output. So, add another called "Compose".
1. 
25. The first part is completed. Now, add an action called "Copy file". We will create a copy of the excel template.
1. 
26. Remember to select "Copy with a new name" option in dropdown which is asking the further course of action "If another file is already there".
1. 
27. Now, here the climax part of the flow. Microsoft have given a unique feature to create table on the fly. Add an action "Create table".
1. 
28. Provide the input as shown below. The starting range is fixed to A1, followed by a colon ":". The table range can be "Absolute", "Partial Relative" or "Relative". Make sure, whatever you are choosing, both cell address must be in same format. Here, I had chosen, "Relative"
29. You may give the table name as per your wish. I had given "SearchResults". You may define a variable for the Table Name as well in the beginning because, this table name is going to be used at two places, therefore, to avoid any misspell, you may use variable.
1. 
30. This was the second part.
31. The last part is adding items to table.
32. Add an action called "Get Items".
1. 
33. Next, we will use the "Select" to transform the data as per our requirement. For an example, if we wish to add another column "Full Name", we can do here by using "concat" function. Remember, the name of column at left side in mapping must be exactly the same as we are expecting to get receive from PowerApps.
34. So, add "Select" action.
1. 
35. I had used "concat" function to join First Name & Last Name. Also, I used "formatDateTime" function to apply formating upon Date Of Joining. 
36. Now, use "Apply to each" action to add this data to excel table.
37. Add an action "Apply to each".
1. 
38. Now, add "Add row into a table" action inside this "Apply to each" action.
1. 
39. It's all done.
40. Now, If you wish to send this excel as attachment or send the link of the file, you can add further actions that will fulfil the requirements. These all, we have already discussed in our previous posts.
41. Save the flow and test it.
42. Let's check for our first set of columns mentioned in the beginning of the post. Remember, column names should be separated either by comma or semicolon and should not have leading or trailing spaces.
1. ID,Title,First Name,Last Name [Correct Format]
2. ID;Title;First Name;Last Name [Correct Format]
3. ID, Title,First Name , Last Name [In-Correct Format]
43. Click on "Test" >> "Manually" >> "Test". It will show the Run window and asking for Column names.
44. Give the input as "ID,Title,First Name,Last Name" and click on Run.
1. 
45. Wait for the flow to complete it's execution. It got succeeded.
1. 
46. Check the SharePoint library. The file got created with name as "EmployeesData1.xlsx".
1. 
47. Here we go. The is there.
1. 
48. Now let's try with custom column 
1. ID,Full Name,Designation,Date Of Joining,Salary,Experience
49. Here we go. This also tested successfully.
1. 
50. This is how, you can create Excel Tables dynamically with any number of columns. However, you can create the file on the runtime but for that, you need to include the OneDrive connection. Ultimately, you are creating a file then copying it. Means one more step. Therefore, we avoided that. Keeping a blank file is far better than to include one more connection and adding one more step.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Power Automate: Useful HTTP Request Actions Part 4

 Hello Friends,

Welcome back with another post on Power Automate. This post is in continuation to one of my earlier post(s) on different ways we can use HTTP request action (Send an HTTP request to SharePoint) in Power Automate to get different kind of information. I will be adding more and more options in this post from time to time. Below is the link of earlier post(s)-

1. Power Automate: Useful HTTP Request Actions Part 1
   
2. Power Automate: Useful HTTP Request Actions Part 2
   
3. Power Automate: Useful HTTP Request Actions Part 3
   

Today, we will learn about below ones-

1. Create SharePoint List
2. Create Column(s)
3. Update Column(s) Display Name
4. Read Excel And Insert Data

Sometimes, there is a requirement to read an uploaded excel and create List for that in SharePoint and insert items in that list. So, either you need to do this activity manually OR you can use Power Automate to achieve the same. We are discussing Power Automate option here.

1. Our process will divide into following steps-
1. Create SharePoint List
2. Create/Rename Columns In List
3. Read Excel from Document Library
4. Add Items from Excel into SharePoint list

Let's start-

1. Create SharePoint List-
1. First we will create custom SharePoint list named "SharePointListTemplateIDs" using Power Automate.
2. Add an action "Send HTTP request to SharePoint".
3. 
4. The Body template used is-

5. {
   
   	"__metadata":{ "type":"SP.List" },
   	"AllowContentTypes":true,
   	"BaseTemplate":100,
   	"Title":"SharePointListTemplateIDs",
   
   	"Description":"This list contains the IDs of different types of lists/libraries we can create."
   
   }
   

6. BaseTemplate is the Id that represents to a  List type. 100 is for Custom list. Rest are as below-
7. 
8. This will create list in SharePoint.
2. Create/Rename Columns In List-
1. We will create/rename below columns-
1. Name: (We will not be creating this column. We will use Title Column and will rename it to Name)
2. TemplateID: (Type: Number; Required: true) 
3. Description: (Type: Text (Multiline); Required: false)
2. For this, we will follow below steps-
1. Rename "Title" to "Name"
2. Create "TemplateID"
3. Rename "TemplateID" to "Template ID"
4. Create "Description"
3. Inputs are-
1. Rename "Title" to "Name"
1. 
2. Create "TemplateID"
1. 
3. Rename "TemplateID" to "Template ID"
1. 
4. Create "Description"
1. 
   
   
3. Read Excel from Document Library-
1. Now we will read the Excel file. We had uploaded the file in Documents library. This file contains the Name/TemplateID/Description columns as shown above in the post.
1. 
4. Add Items from Excel into SharePoint List-
1. 
5. That's all. Now save the flow and execute it.
6. Here's the result-
1. 
7. Please feel free to sent me your queries or any functionality that you want to achieve and currently not able to do so. I would be happy, if would be able to provide it's solution.
8. Next Post Link-
1. Coming soon...

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Power Automate: Useful HTTP Request Actions Part 3

Hello Friends,

Welcome back with another post on Power Automate. This post is in continuation to one of my earlier post(s) on different ways we can use HTTP request action (Send an HTTP request to SharePoint) in Power Automate to get different kind of information. I will be adding more and more options in this post from time to time. Below is the link of earlier post(s)-

1. Power Automate: Useful HTTP Request Actions Part 1
   
2. Power Automate: Useful HTTP Request Actions Part 2
   

Today, we will learn about below ones-

1. Create Column(s)
2. Update Column(s) Display Name

Sometimes, there is a requirement to create lots of columns in SharePoint list. They may be "Single line of text" or "Number" column. So, either you need to create manually one by one OR you can use Power Automate to achieve the same. We are discussing Power Automate option here.

1. In order to create columns, first we need an array of columns information

1. [
   	{
   		"ColumnName":"Column1Text",
   		"ColumnType":"Text",
   		"ColumnTypeKind":2,
   		"Required":"false"
   	},
   	{
   		"ColumnName":"Column2Number",
   		"ColumnType":"Number",
   		"ColumnTypeKind":9,
   		"Required":"false"
   	},
   	{
   		"ColumnName":"Column3Text",
   		"ColumnType":"Text",
   		"ColumnTypeKind":2,
   		"Required":"true"
   	},
   	{
   		"ColumnName":"Column4Number",
   		"ColumnType":"Number",
   		"ColumnTypeKind":9,
   		"Required":"true"
   	},
   	{
   		"ColumnName":"Column 5 With Space",
   		"ColumnType":"Text",
   		"ColumnTypeKind":2,
   		"Required":"false"
   	}
   ]
   

2. Here, you can see, I have taken a field ColumnDataKind. Basically, it represents the type of column. For more details, you may refer Microsoft docs site-
1. FieldType enumeration
   
3. For Text column it is 2 and for Number column, it is 9.
4. Now, we need a SharePoint List-
1. 
5. I had created a list named "DynamicColumnsList". It is be default having one column "Title".
6. Now we are going to create below columns-
1. Column1Text (Type: Text; Required: false) 
2. Column2Number (Type: Number; Required: false)
3. Column3Text (Type: Text; Required: true)
4. Column4Number (Type: Number; Required: true)
5. Column 5 With Space (Type: Text; Required: false)
7. Let's create a flow-
1. I had initialized a string variable "strColumnsJSONString" and assigned the above mentioned array to this variable.
1. 
2. Now, I am going to parse it. For this, I will use Parse JSON action. I will use same JSON string to create the schema.
1. 
3. Lastly, I will use HTTP Action-
1. We will use below schema to create columns in Body section and then will assign dynamic values from JSON.

2. {
   	"__metadata":
   		{
   			"type":"SP.Field"
   		},
   	"FieldTypeKind":,
   	"Title":"",
   	"Required":"",
   	"EnforceUniqueValues":"false",
   	"StaticName":""
   }

3. Final HTTP action is-
4. 
8. Save it and test it.
9. 
10. As you can see, columns have been created along with the Required (Yes/No) condition. The moment, columns are created, it's Internal Name also get created with same name and gets freezes. In case, if the name has spaces, it replaces with "_x0020_".
11. Now, if you want to change the Display Name, then you need to repeat the process but this time we will use MERGE.
12. Below is the JSON used-

1. [
   	{
   		"ColumnName":"Column1Text",
   		"ColumnType":"Text",
   		"ColumnTypeKind":2,
   		"Required":"false",
   		"DisplayName":"Column 1 Text"
   	},
   	{
   		"ColumnName":"Column2Number",
   		"ColumnType":"Number",
   		"ColumnTypeKind":9,
   		"Required":"false",
   		"DisplayName":"Column 2 Number"
   	},
   	{
   		"ColumnName":"Column3Text",
   		"ColumnType":"Text",
   		"ColumnTypeKind":2,
   		"Required":"true",
   		"DisplayName":"Column 3  Text"
   	},
   	{
   		"ColumnName":"Column4Number",
   		"ColumnType":"Number",
   		"ColumnTypeKind":9,
   		"Required":"true",
   		"DisplayName":"Column 4 Number"
   	},
   	{
   		"ColumnName":"Column 5 With Space",
   		"ColumnType":"Text",
   		"ColumnTypeKind":2,
   		"Required":"false",
   		"DisplayName":"Column 5 With Spaces"
   	}
   ]
   

13. Same will be used in initializing the string variable as well as in Generate Sample in Parse JSON.
14. Now the HTTP action will be-
1. 
15. Again Save the flow and test it.
1. 
16. As we can see, the Display Name & the Internal Name are different as we wish to have. This way, we can create and rename the columns in SharePoint list using Power Automate flow.
17. For adding Choice column (Drop-down menu)-
1. Input-
1. 
2. Output-
1. 
18. Please feel free to sent me your queries or any functionality that you want to achieve and currently not able to do so. I would be happy, if would be able to provide it's solution.
19. Next Post Link-
1. Power Automate: Useful HTTP Request Actions Part 4
   

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

PowerApps: Add/Drop/Show/Rename Column

Hello Friends,

Welcome back with some new topics. Today, we will discuss about Column operations in PowerApps. PowerApps provides below options to play on columns-

1. AddColumns
2. ShowColumns
3. DropColumns
4. RenameColumns

These functions are used to re-shape the collection/table in PowerApps. Apart from that, it also provide functionality to get distinct data using Distinct function.

In this post, we will learn about:

1. Add/Show/Drop/Rename Column functions
2. Distinct function
3. Adding Blank Record (or "Please Select") at Beginning/End of collection (beneficial when binding with Dropdown)
   
4. Add ID using Filter function in collection having Distinct data

Let's start one by one. 

1. First we will create a collection. I had created a collection named "collSampleData". The data is:

2. ClearCollect(
       collSampleData,
       {
           ID: 1,
           FName: "Sachin",
           LName: "Jain"
       },
       {
           ID: 2,
           FName: "Lokesh",
           LName: "Kumar"
       },
       {
           ID: 3,
           FName: "Rajeev",
           LName: "Khanna"
       },
       {
           ID: 4,
           FName: "Anuj",
           LName: "Sharma"
       },
       {
           ID: 5,
           FName: "Rupesh",
           LName: "Jaiswal"
       },
       {
           ID: 6,
           FName: "Anuj",
           LName: "Sharma"
       },
       {
           ID: 7,
           FName: "Sachin",
           LName: "Jain"
       },
       {
           ID: 8,
           FName: "Sanjiv",
           LName: "Sharma"
       },
       {
           ID: 9,
           FName: "Rajeev",
           LName: "Sharma"
       }
   );  
   

3. It will create a collection with 9 records. Here I had added couple of duplicate entries which we will use for Distinct function.
4. 
5. If we check the collection, it has:
6. 
   
   
   
   
7. Add Column (AddColumns):
1. AddColumns function is used to add column to the collection/table. You may add a calculated column, add and existing column with different datatype. I am using to add a calculated column by adding FName & LName.

2. ClearCollect(
       collWithFullName,
       AddColumns(
           collSampleData,
           "FullName",
           FName & " " & LName
       )
   );
   

3. 
   
   
4. If we check the collection, it has:
   
5. 
   
   
6. This way, we can add columns to collection.
   
8. Show Column (ShowColumns)
1.  ShowColumns is used to include only those columns in table/collection that needs to be displayed and rest needs to be dropped.

2. ClearCollect(
       collShowColumnData,
       ShowColumns(
           collWithFullName,
           "ID",
           "FullName"
       )
   );
   

3. 
4. If we check the collection, it has:
5. 
   
6. Clearly, we can see, only 2 columns are retained and rest are removed. This is useful when we need to show a couple of columns from a large columns list.
9. Drop Column (DropColumns)
1.  DropColumns is used to drop the columns from table/collection that needs not to be displayed. It can be say a opposite of ShowColumns.

2. ClearCollect(
       collWithOnlyFullName,
       DropColumns(
           collWithFullName,
           "ID",
           "FName",
           "LName"
       )
   );
   

3. 
   
4. If we check the collection, it has:
5.  
   
   
6. Clearly, we can see, all 3 columns are dropped and rest only 1 column is retained. This is useful when we need to drop couple of columns from a large columns list and rest to be retained.
10. Distinct (Distinct)
1. Before moving forward to RenameColumns, we will discuss about Distinct function. After this, you will better understand the RenameColumns function. Distinct function is used to get the distinct values from a table/collection. Distinct works only on single column and return that particular column from the collection.
   

2. ClearCollect(
       collDistinctData,
       Distinct(
           collWithOnlyFullName,
           FullName
       )
   );
   

3. 
   
   
   
4. If we check the collection, it has:
5. 
   
11.  Rename Column (RenameColumns)
1. RenameColumns is used to rename the name of existing column.

2. ClearCollect(
       collRenameColumnData,
       RenameColumns(
           collDistinctData,
           "Result",
           "Full Name"
       )
   );
   

3. 
   
   
   
4. If we check the collection, it has:
5. 
   
   
   
6. As we can check the column name has been renamed from "Result" to "Full Name" while the data remains unchanged.
   
12. Nested Use Of These Functions
    
13. Now, once you are expertise in using functions, you may consolidate all these functions into one. I am using below function in one command to achieve the same functionality-
1. AddColumns
2. DropColumns
3. RenameColumns
4. Distinct

14. ClearCollect(
        collConsolidated,
        RenameColumns(
            Distinct(
                DropColumns(
                    AddColumns(
                        collSampleData,
                        "FullName",
                        FName & " " & LName
                    ),
                    "FName",
                    "LName"
                ),
                FullName
            ),
            "Result",
            "Full Name"
        )
    );
    

15. 
    
    
    
16. If we check the collection, it has:
17. 
    
    
    
18. Add Blank Record At Beginning/End of Collection
19. We have consolidated data collection. We will add a blank record at the end of collection.
    

20. ClearCollect(
        collBlankAtEnd,
        collConsolidated,
        {'Full Name': Blank()}
    );
    

21. 
    
    
22. For the purpose to display blank record, I had set border of labels used.
    
23. Now, we will add a blank record at the beginning of collection.

24. ClearCollect(
        collBlankAtBegin,
        {'Full Name': Blank()},
        collConsolidated
    );  
    

25. 
    
    
26. Add ID using Filter function in collection having Distinct data
27. Now suppose a scenario, where you have a collection which is having multiple duplicate records. Your objective is to have Distinct records having ID and Name. The problem is that Distinct function returns only single column, so what the approach is?
    
1. We will create a collection.
2. Apply Distinct (to get distinct records) >> AddColumns (add ItemID column with default value as 0) >> RenameColumns (rename the column "Result" created as as result of Distinct function to ItemName).
3. Create a replica of this distinct collection to apply ForAll loop.
4. Apply ForAll loop. In this loop apply Patch. In Patch, use Filter with First function to get the desired ItemID of each item.
28. Sequentially code is as below-

1. ClearCollect(
       collStationeryData,
       {
           ID: 1,
           ItemName: "Pencil"
       },
       {
           ID: 2,
           ItemName: "Pen"
       },
       {
           ID: 3,
           ItemName: "Eraser"
       },
       {
           ID: 2,
           ItemName: "Pen"
       },
       {
           ID: 4,
           ItemName: "Sharpner"
       },
       {
           ID: 5,
           ItemName: "Paper A4"
       },
       {
           ID: 1,
           ItemName: "Pencil"
       },
       {
           ID: 3,
           ItemName: "Eraser"
       },
       {
           ID: 6,
           ItemName: "Stapler"
       }
   );
   

2. ClearCollect(
       collDistinctStationeryData,
       RenameColumns(
           AddColumns(
               Distinct(
                   collStationeryData,
                   ItemName
               ),
               "ItemID",
               0
           ),
           "Result",
           "ItemName"
       )
   );
   

3. ClearCollect(
       collDistinctStationeryDataTemp,
       collDistinctStationeryData
   );
   

4. ForAll(
       collDistinctStationeryDataTemp,
       Patch(
           collDistinctStationeryData,
           LookUp(
               collDistinctStationeryData,
               ItemName = collDistinctStationeryDataTemp[@ItemName]
           ),
           {
               ItemID: First(
                   Filter(
                       collStationeryData,
                       ItemName = collDistinctStationeryDataTemp[@ItemName]
                   )
               ).ID
           }
       )
   );  
   

29. 
    
30. If we check the collection, it has:
1. collStationeryData
   
2. 
   
   
3. collDistinctStationeryData (initial stage before Patch) / collDistinctStationeryDataTemp
   
4. 
   
   
5. collDistinctStationeryData (After Patch)
6. 
   
   
31. This way, you can utilize these power packed functions as per the required. Even you may nest these functions to avoid multiple steps.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !