How to do a Pivot Table using a Linq Query in UiPath?

[Kakooza-Allan-Klaus]

Hello Good People,

Am trying to use a linq query to do a pivot table but am getting this error:

Assign: Input array is longer than the number of columns in this table.

This the resource I’ve been trying to follow:

LINQ | LINQ Pivot | Create Pivot Table Using LINQ | LINQ Uipath | Uipath Tutorials | Coderslobby

Below is the linq query am trying to use:

(From row In dtBandPushing.AsEnumerable
Group row By Key = New With{
	Key.TERMINAL = row.item("TERMINAL"),
	Key.COMMISSION_EARNED =row.ITEM("COMMISSION_EARNED")
} Into grp = Group
Select dtBandPushing.LoadDataRow(New Object(){
	Key.TERMINAL,
	Key.COMMISSION_EARNED,
	grp.Count()},True)
).CopyToDataTable

Kindly advise on what I could doing wrong.

Thank you.

Regards,
Kakooza Allan Klaus

[kumar.varun2 (Varun Kumar)]

Hi @Kakooza-Allan-Klaus

• Create a new Data Table Variable - dtBandPushingGrouped

• Assign : dtBandPushingGrouped = dtBandPushing.Clone

• Add new data column (object type) to dtBandPushingGrouped using the Add Data Column Activity

• Assign
  dtBandPushingGouped =

(
    From row In dtBandPushing.AsEnumerable
    Group row By
    k1 = row("TERMINAL"),
    k2 = row("COMMISSION_EARNED")
    Into grp = Group
    Select dtBandPushingGrouped.Rows.Add({k1, k2, grp.Count()})
).CopyToDataTable

Hope it works!

[Kakooza-Allan-Klaus]

@kumar.varun2

• Add new data column (object type) to dtBandPushingGrouped using the Add Data Column Activity

Why am I adding this column and what values should I populate there:

[Kakooza-Allan-Klaus]

That’s the error I get when I try to write range

[hemanth.bhat]

Hi @Kakooza-Allan-Klaus ,

Kindly share the input excel file and the expected output,
to provide you with a linq query to perform pivot

[Kakooza-Allan-Klaus]

Input_File.xlsx (21.3 KB)

Expected_Output.xlsx (35.6 KB)

Those are the files that you’ve requested for.

Thanks

[Kakooza-Allan-Klaus]

@hemanth.bhat,
Maybe to add more context to this @kumar.varun2’s linq query worked but I don’t know why he appends another column to the DataTable and on writing the data to an excel file I get that error of Failed setting the current sheet to: Export in workbook

[kumar.varun2 (Varun Kumar)]

Hi @Kakooza-Allan-Klaus

Try this approach

dt_Pivot=

(
	From row In dt_Data.AsEnumerable
	Group row By k = row("TERMINAL")
	Into grp=Group
	Select dt_Data.LoadDataRow({k, grp.Sum(Function(gr) CDbl(gr("COMMISSION_EARNED")))}, True)
).CopyToDataTable

Xaml for reference

PivotTableUsingLINQ.xaml (5.4 KB)

[Kakooza-Allan-Klaus]

Thanks a lot @kumar.varun2,

This resolved my issue.

And the Failed Setting error was resolved when I used Workbook activities instead of the Excel Application Scope Activities.

And also do you’ve any resource that you could recommend that would help me improve my skill with linq queries.

[kumar.varun2 (Varun Kumar)]

Hi @Kakooza-Allan-Klaus

The master of LINQ (@ppr ) has created a full catalogue with links to various resources. Please check out.

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operators Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start # All Operato…

[Kakooza-Allan-Klaus]

Thanks @kumar.varun2,

I highly appreciate man

@ppr has actually helped resolve a number of bugs here.

[hemanth.bhat]

Thanks @kumar.varun2 for the solution