Linq query to find data and update to the rest cells into datatable

[yashashwini2322 (Yash)]

Hi all,

I.m facing an issue while finding and updating the value in datatable

For example below is the scenario

In the above screenshot,I want to find the last cell value of column b ,if the value is null or empty then I have to search for the previous cell value of same column b and fetch that value and update to the rest cells of column b .
Please guide me how can I achieve this.

Below is the output screenshot I have attached for reference

[supriya117 (Supriya Allada)]

Hi @yashashwini2322

Try vb code activity:

For Each row In dt.AsEnumerable()
    Dim rowIndex As Integer = dt.Rows.IndexOf(row)
    Dim previousValue As String = If(rowIndex > 0, dt.Rows(rowIndex - 1)("ColumnB").ToString(), "")
    Dim currentValue As String = row("ColumnB").ToString()
    
    If String.IsNullOrEmpty(currentValue) Then
        dt.Rows(rowIndex).SetField("ColumnB", previousValue)
    End If
Next

[rlgandu (Rajyalakshmi Gandu)]

@yashashwini2322

For Each row As DataRow In dtInput.Rows
    Dim rowIndex As Integer = dtInput.Rows.IndexOf(row)
    If rowIndex > 0 AndAlso String.IsNullOrWhiteSpace(row(7).ToString()) Then
        row(7) = dtInput.Rows(rowIndex - 1)(7)
    End If
Next

Mention your column index or column name here my column index is 7 so i give it as 7

[vrdabberu (Varunraj Dabberu)]

Hi @yashashwini2322

→ Read Range Workbook (Remove Add headers in properties panel)

Output-> dt_Input

→ Use below syntax in Assign activity:

- Assign-> lastValue = yourDataTable.AsEnumerable().Where(Function(row) Not String.IsNullOrEmpty(row(1).ToString())).LastOrDefault()(1).ToString().Trim()

- Assign-> dt_Output = (From row In dt_Input.AsEnumerable()
                    Let newValue = If(String.IsNullOrEmpty(row(1).ToString().Trim()), lastValue, row(1).ToString())
                    Select dt_Input.Clone().Rows.Add(row(0).ToString(), newValue)
                   ).CopyToDataTable()

lastValue is of DataType System.String and dt_Output is of DataType System.Data.DataTable.

→ Write Range Worbook dt_Output datatable

Sequence25.xaml (8.7 KB)

Regards

[Yoichi]

Hi,

FYI, another approach: we can achieve it using FindFirst/LastRow and AutoFill as the following sample.

Sample
Sample20240209-1.zip (14.9 KB)

Regards,