Скачать с ютуб Data Table Operation In Power Automate Desktop using Run .Net в хорошем качестве

Data Table Operation In Power Automate Desktop using Run .Net

Hello All, In this session we will learn about most wanted data table operation in Power Automate Desktop. There are no actions available to deal with filtering Data tables in PAD, #powerautomatedesktop. Watch this video till the end and share your feedback. Sessio Details: References to be loaded: For some of the actions that are performed using this action, a few references need to be imported. Since we are focusing on the data table in this article, we will import references related to the data table. Below is the process to load the assembly references. This step is crucial, as some of the LINQ queries used in this article reference functions like AsEnumerable(), CopyToDataTable(), etc. The following steps are mandatory to ensure they work without errors: • Create a new folder at this location: C:\PowerAutomateDesktop\AssemblyReferences (you can choose any folder name as per your preference). • Navigate to the directory: C:\Windows\Microsoft.NET\Framework64\v4.0.30319 (the version number may vary depending on the .NET framework installed on your system). • Copy the file named System.Data.DataSetExtensions.dll from step 2 to the newly created folder in step 1. • Set the value C:\PowerAutomateDesktop\AssemblyReferences in the "References to be loaded" field within the Run .NET Script action. 1. Clone Data Table Creates a new DataTable with the same structure as the original DataTable, without copying any data. Imagine you have an Excel file with 20 columns. To create another table that looks the same, you would typically need to create a new table and add 20 columns again. The 'clone' method helps you bypass all that additional effort. 2. Remove Duplicate rows Remove duplicates based on all columns resultTable = TrainingTable.DefaultView.ToTable(True) Remove duplicates based on the specific column 'Month Name'. If there are multiple entries, the first entry will be retrieved using the following query: • g.First: Retrieves the first occurrence from the duplicate rows. • g.Last: Retrieves the last occurrence from the duplicate rows. resultTable = TrainingTable.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("Month Name")).Select(Function(g) g.First).CopyToDataTable Remove duplicates based on multiple columns 'Month Name' and 'Consultant'. If there are multiple entries then first entry will be fetched using below query. You can modify the query for multiple columns as needed. 3. Get Certain columns from a datatable Retrieve only the necessary columns from the datatable. Using False as the first argument retrieves all rows, including duplicates. Using True as the first argument retrieves only unique rows. resultTable = TrainingTable.DefaultView.ToTable(False, "Month Name", "CONSULTANT", "City") 4. Get Top N rows in a datatable Retrieves the initial N records from the datatable. The query below retrieves the first 5 rows from the TrainingTable. resultTable = TrainingTable.AsEnumerable().Take(5).CopyToDataTable() 5. Skip first N rows in a datatable Skips the initial N records in the datatable. The following query fetches all the rows from the TrainingTable while skipping the first 5 rows. resultTable = TrainingTable.AsEnumerable().Skip(5).CopyToDataTable() 6. Sort the table Ascending order resultTable = (From row In TrainingTable.AsEnumerable( Order By Convert.ToInt32(row("MonthNum")) Select row).CopyToDataTable()) Descending order : resultTable = (From row In TrainingTable.AsEnumerable( Order By Convert.ToInt32(row("MonthNum")) Descending Select row).CopyToDataTable()) custom sort : 7. Filter Records Filtering by a Single Value in a Column. resultTable = TrainingTable.Select("[Month Name] ='February'").CopyToDatatable() Filtering by Multiple Values in a Column. resultTable = TrainingTable.Select("[Month Name] ='"+month1+"' OR [Month Name] ='"+month2+"' OR [Month Name] ='"+month3+"'").CopyToDatatable Filtering based on multiple columns. resultTable = TrainingTable.Select("[Month Name] ='January' AND [CONSULTANT] = 'User Name'").CopyToDatatable 8. Insert Column in the datatable Inserts the columns at the end of the datatable. TrainingTable.Columns.Add("Status", GetType(String)) TrainingTable.Columns.Add("UniqueID", GetType(Int32)) Inserts the column at the beginning of the datatable. TrainingTable.Columns.Add("Remarks", GetType(String)).SetOrdinal(0) Inserts the column at a specific index in the datatable. TrainingTable.Columns.Add("Remarks", GetType(String)).SetOrdinal(index) 9. Delete column in the datatable Deletes a column from a datatable based on either the column name or the column index. TrainingTable.Columns.RemoveAt(index) 'removes column at specified index. TrainingTable.Columns.Remove('ColumnName') 'removes column based on name. 10.merge two datatables tblA.Merge(tblB)