Excel Filter Question!

If you group the data, it wont be able to get a proper datatable back since it will be in a collection format. one method is to use a for each getting each item in groupedData and writing the currentItem to excel

1 Like

How about this error ?

image

dtInputData is not declared in variables or its scope is not reachable there

How to correct this error ??? Any idea ?

(From row In dtInputData.AsEnumerable()
Group row By ModelPrefix = If(row(“Model”).ToString().Length >= 4, row(“Model”).ToString().Substring(0, 4), row(“Model”).ToString()) Into Group
Let GroupedModels = String.Join(“,”, Group.Select(Function(r) r(“Model”).ToString()))
Select dtInputData.Rows.Add({ModelPrefix, GroupedModels})).CopyToDataTable()
Try using this instead

@170290064 Create another datatable(eg:dt_result) of the same columns. Then use invoke code and paste this in the activity

dtResultsPage.AsEnumerable.GroupBy(Function(row) row.Field(Of String)(“Model”).Split(" "c)(0)).SelectMany(Function(group) group).ToList.ConvertAll(Function(row) dt_result.Rows.Add(row(0),row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8)))

This will store the grouped items in the Datatable dt_result. Make sure to import arguments into the invoke code activity

I am actually trying to get more proper excel table with model,km,year and price

it filtered but looks like not even a table.

Lemme try this one as well.

@170290064 If you have used ‘write range workbook’ activity make sure to select the ‘add headers’ option in the properties panel.

(dtResultsPage.AsEnumerable.OrderBy(Function(row) If(row(“Model”).ToString().Length >= 4, row(“Model”).ToString().Substring(0, 4), row(“Model”).ToString()))).CopyToDataTable

You can try this query to order the excel based on the first four letters of model. So all 320i will be one after the other then all 318i will be together and so forth

image

Wherever double quotes is present, try changing it and trying again or if you want the code you can access from the below xaml
Sequence6.xaml (6.3 KB)

You can alternatively refer this link to solve assign activity - VisualBasicValue1 errors : Assign: Expression Activity type 'VisualBasicValue`1' requires compilation in order to run. Please ensure that the workflow has been compiled

1 Like

Alright!

Lemme try it!

Also to solve this method,
(dtResultsPage.AsEnumerable.GroupBy(Function(row) row.Field(Of String)(“Model”).Split(" "c)(0)).SelectMany(Function(group) group).ToList.ConvertAll(Function(row) dt_result.Rows.Add(row(0),row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8)))).CopyToDataTable

May convert it to datatable instead of list(Of Datarow)

@170290064 Try this workflow

Main.xaml (6.7 KB)
sample.xlsx (21.4 KB)
Output.xlsx (16.4 KB)

1 Like

Okay but i want output table to be like in the following.

“Model” , “Yıl” , “KM” , “Fiyat”

To get the data in the format mentioned you can use the linq,
(From row In dtInput.AsEnumerable()
Group row By ModelPrefix = If(row(“Model”).ToString().Length >= 4, row(“Model”).ToString().Substring(0, 4), row(“Model”).ToString()) Into Group
Let GroupedYıl = String.Join(“,”, Group.Select(Function(r) r(“Yıl”).ToString()))
Let GroupedKM = String.Join(“,”, Group.Select(Function(r) r(“KM”).ToString()))
Let GroupedFiyat = String.Join(“,”, Group.Select(Function(r) r(“Fiyat”).ToString()))
Select dtOutput.Rows.Add({ModelPrefix, GroupedYıl,GroupedKM,GroupedFiyat})).CopyToDataTable()

To check it out the sample xaml is given here :
Model Excel.xaml (8.2 KB)

And the output file is :
Output_Model.xlsx (8.5 KB)

3 Likes

Thanks for all answers and efforts :clap:

@Brian_Mathew_Maben
@Vikas_M
@Quenton_Wayne_Rebello
@Yazan_Otaibi

1 Like

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