Adding rows values into new column using linq

[mkankatala (Mahesh Kankatala)]

Hi @Suma1993

Using linq query to create pivot table for multiple columns

Hello @Suma1993 You can use the VB code in Invoke code activity to create required excel with the Input data, → Use the Read range workbook activity to read the excel and store in a datatable called Input_dt. → Use the assign activity to initialize the output datatable, create Output_dt variable which is Datatable datatype, - Assign -> Output_dt = New Data.DataTable() → Use the Invoke code activity and click on edit code give the below code in it, ' Initialize the output DataTable Dim o…

You have used the vb code in the Invoke code activity in the above post right, Remove that code in Invoke code activity give the below one,

' Initialize the output DataTable
Dim outputDt As New DataTable
outputDt.Columns.Add("Date")

' Get distinct Invoice Dates to use as columns
Dim invoiceDates = inputDt.AsEnumerable().Select(Function(row) row("Invoice Date").ToString()).Distinct().ToList()

' Add columns for each distinct Invoice Date
For Each dateStr In invoiceDates
    outputDt.Columns.Add(dateStr)
Next

' Add Total column
outputDt.Columns.Add("Total", GetType(Integer))

' Get distinct Dates to use as rows
Dim dates = inputDt.AsEnumerable().Select(Function(row) row("Date").ToString()).Distinct().ToList()

' Populate the output DataTable
For Each dateStr In dates
    Dim newRow As DataRow = outputDt.NewRow()
    newRow("Date") = dateStr
    
    Dim totalRowAmount As Integer = 0
    
    For Each invoiceDate In invoiceDates
        Dim totalAmount = inputDt.AsEnumerable().
            Where(Function(row) row("Date").ToString() = dateStr AndAlso row("Invoice Date").ToString() = invoiceDate).
            Sum(Function(row) Convert.ToInt32(row("Net Amount")))
        If totalAmount > 0 Then
            newRow(invoiceDate) = totalAmount
            totalRowAmount += totalAmount
        End If
    Next
    
    newRow("Total") = totalRowAmount
    
    outputDt.Rows.Add(newRow)
Next

' Assign the result to the output argument
Output_dt = outputDt

→ The Arguments and flow is same.

Check the below excel output for better understanding,

Hope it helps!!