Using linq query to create pivot table for multiple columns

[Suma1993 (Suma)]

Hi everyone,
I have an excel which consists of Columns “Date”,“invoice date” and net amount.
Can anyone please help me to get the below output using linq query pivot tables

columns are from “invoice date” column value
rows are from " date" column value
the values for each row from “net amount” column

[mkankatala (Mahesh Kankatala)]

Hi @Suma1993

Could you share us the input data as well, better to be in excel file.

[Suma1993 (Suma)]

Hi @mkankatala ,please find the sample input file

[Suma1993 (Suma)]

@mkankatala , please find sample input file
Sample.xlsx (8.6 KB)

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

[Suma1993 (Suma)]

Thank You so much @mkankatala , its working perfectly as expected.
Can you also please let me know how to sum the amount in each row of final datatable and add it at last? please find the screenshot for reference

[mkankatala (Mahesh Kankatala)]

It’s my pleasure… @Suma1993

I hope you find the solution for query, Make my post mark as solution to close the query.

For furhter queries create a new post there we will give solutions for this.

Happy Automation!!

[Suma1993 (Suma)]

Sure, Thank you for your valuable time @mkankatala