Need LINQ query to filter Excel

Help Activities
, , , ,
1

Hi Everyone,

I need to filter excel data based on specific column. I have a LINQ query but it is not giving correct output. I’m attaching my sample excel below.

my LINQ query:

(From row In ReadDT.AsEnumerable().Skip(1)
Group row By orderNo = row.Field(Of String)(“OrderNo”) Into Group
Select Group.First()).CopyToDataTable()

Input Sheet : my input
RequiredOutput : I need output in that format
Sheet2 : This is the output given by LINQ query

SampleExcel.xlsx (11.6 KB)

2

@Learner007

Please try this

(From row In ReadDT.AsEnumerable().Skip(1) Group row By orderNo = row.Field(Of String)("OrderNo") Into Group Group.OrderByDescending(function(x) CDBL(x("Shipment Ampunt").ToString.Replace("$",""))) Select Group.First()).CopyToDataTable()

Not sure why you are usinng skip(1) though…

Cheers

3

Hi @Anil_G Actually i was getting additional columns that’s the reason i used Skip but it didn’t worked.

I will try your code now

4

Hi,

Can you try the following? This outputs what you need.

image
image730×437 17.3 KB

Sample20230607-1L.zip (11.5 KB)

Regards,

5

Hi @Yoichi Do we get difference by using Windows or Legacy. I’m using Windows and if i write the above code it is not giving output correctly. But the flow which you have sent that is working. Do i need to change anything for this code to work in Windows

6

Hi,

I think it’s not caused by difference b/w Windows and Windows-Leagcy. Can you try the following Windows project?

Sample20230607-3.zip (11.4 KB)

And if possible, can you share your workflow?

Regards,

7

image
image

8

Hi @Yoichi This is my flow
BlankProcess6.zip (144.9 KB)

9

Hi @Yoichi I’m getting below error when i try to open the code.
dfgh

10

Hi @sasi_poosarla This is giving error while writing the code.

11

send that error once

12

Hi,

There are some settings in cells of ColumnM. It causes the datatable has extra columns.

image
image956×584 32.7 KB

For now, can you try to remove ColumnM then run? Please remove Skip(1) as the following from the expression.

ReadDT.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("OrderNo")).Select(Function(Group) Group.First()).CopyToDataTable()

Regards,

1 Like
13

Thanks @Yoichi It worked

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.