Using linq query to create pivot table for multiple columns

[mkankatala (Mahesh Kankatala)]

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 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

' 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
    
    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
        End If
    Next
    
    outputDt.Rows.Add(newRow)
Next

' Assign the result to the output argument
Output_dt = outputDt

→ Click on Import arguments and give the Input_dt with in direction and Output_dt with Out direction,

→ Then use the write range workbook activity to write the Output_dt variable to the excel.

Check the below workflow for better understanding,
Sequence5.xaml (11.6 KB)

Check the below screenshot of Output Excel,

Hope it helps!!