Invoke C# code

i have a datatable SrcDt with the below structure

SrcDt:

                           Column0          |                   Column1
---------------------------------------------------------------------------
0  **Occupational Therapy (66)** |                     null/blank
----------------------------------------------------------------------------        
1                  - avg Age    |                      <Sheet1_1_0>
---------------------------------------------------------------------------
2               - Avg.Visits    |                       <Sheet1_2_0>
-------------------------------------------------------------------------------
3      **Physiotherapists (72)**     |                               null/blank
-------------------------------------------------------------------------------
4                  - avg Age          |                      <Sheet1_4_0>
-------------------------------------------------------------------------------
5               - Avg.Visits      |                       <Sheet1_5_0>
-------------------------------------------------------------------------------
6        **Social workers (89)**  |             null/blank             
-------------------------------------------------------------------------------
7                  - avg Age   |                      <Sheet1_7_0>
-------------------------------------------------------------------------------
8               - Avg.Visits     |                       <Sheet1_7_0>
-------------------------------------------------------------------------------

the desire output is as below i have managed to do it in Python but want to see if i can do it in VB

df.loc[df[1].isna(), 'work_type'] = df[0]
df1 = df.assign(work_type=df['work_type'].ffill()).dropna(subset=[1])

print(df1[['work_type',0,1]])

OutDt:
       work_type            | Column1             |Column2
1  Occupational Therapy (66)     |- avg Age  |<Sheet1_1_0>
2  Occupational Therapy (66)  |- Avg.Visits  |<Sheet1_2_0>
4      Physiotherapists (72)     |- avg Age  |<Sheet1_4_0>
5      Physiotherapists (72) |- Avg.Visits  |<Sheet1_5_0>
7        Social workers (89)     |- avg Age  |<Sheet1_7_0>
8        Social workers (89)  |- Avg.Visits  |<Sheet1_7_0>
....

I have this below code how can i run in my flow

var dict = new Dictionary<string, List<(string, string)>>();
// loop all rows but start with the first that contains a work_type(where the second column is empty/null)
string lastWorkType = null;
foreach (DataRow row in SrcDt.AsEnumerable().SkipWhile(r => !r.IsNull(1)))
{
    if (row.IsNull(1))
    {
        string workType = row.Field<string>(0);
        dict[workType] = new List<(string, string)>();
        lastWorkType = workType;
    }
    else
    {
        dict[lastWorkType].Add((row.Field<string>(0),row.Field<string>(1)));
    }
}

var OutDt = new DataTable();
OutDt.Columns.Add("work_type");
OutDt.Columns.Add();
OutDt.Columns.Add();
foreach(var kv in dict)
{
    foreach(var fields in kv.Value)
    {
        OutDt.Rows.Add(kv.Key, fields.Item1, fields.Item2);
    }
}

Hi,

did you try to put the code into the “Invoke Code” activity and select “Csharp”?
image

Just remember that you can not declare a list in C# like this list<string, string>.

regards, Gio

@q.gio i guess thats where i failed to use that code how else can i declare that

Because this < string , string > should be a dictionary, to declare a list is enough this:
newList = new List<string>();

In your case, you could use a list of data rows to store all the rows or other data type (ex. Tuples):

listRows = new List<DataRow>();

Gio

i will try that thank you