datatable in vb.net keep rightmost column with data in and remove empty columns

Question

I have a DataTable with the below data which I'm wanting to format and just have the most recent weeks data.

ID	Name	Week 1	Week 2	Week 3	Week 4
1	Conor	100	87	3	0
2	Frank	35	70	0	0
3	Jeff	35	13	0	57

I would like to keep the first 2 columns and then keep the right most column that isn't 0 giving me the following

ID	Name	Value
1	Conor	3
2	Frank	70
3	Jeff	57

I'm quite new to LINQ so I'm a little unsure if it's possible to do this or not so any help would be appreciated.

Additional Info: I forgot to mention that I'm creating the solution in UiPath (an RPA tool) so although VB Code would be better for this instance LINQ is preferable.

Answer 1

Linq is cool but working code is even cooler. Linq isn't necessarily faster. It does the loops internally.

Your code in the GetDataTable function would be the extraction of the data from Excel. I just built a DataTable to match your example.

In the button click event I created a table to hold the result. The outer loop goes through each row in the source DataTable. The inner For loop starts at the right most column in the dtSource and steps back to the third column (index 2). Note the Step -1. This should work for any number of Week columns since we use dtSource.Columns.Count - 1 As soon as it finds an non zero value it adds a record to dtResult and exits the inner For going on to the next row in dtSource.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dtSource = GetDataTable()
    Dim dtResult As New DataTable
    dtResult.Columns.Add("ID", GetType(Integer))
    dtResult.Columns.Add("Name", GetType(String))
    dtResult.Columns.Add("Value", GetType(Integer))
    For Each row As DataRow In dtSource.Rows
        For i = dtSource.Columns.Count - 1 To 2 Step -1
            If CInt(row(i)) <> 0 Then
                dtResult.Rows.Add({row("ID"), row("Name"), row(i)})
                Exit For
            End If
        Next
    Next
    DataGridView1.DataSource = dtResult
End Sub

Private Function GetDataTable() As DataTable
    Dim dt As New DataTable
    dt.Columns.Add("ID", GetType(Integer))
    dt.Columns.Add("Name", GetType(String))
    dt.Columns.Add("Week1", GetType(Integer))
    dt.Columns.Add("Week2", GetType(Integer))
    dt.Columns.Add("Week3", GetType(Integer))
    dt.Columns.Add("Week4", GetType(Integer))
    dt.Rows.Add({1, "Conor", 100, 87, 3, 0})
    dt.Rows.Add({2, "Frank", 35, 70, 0, 0})
    dt.Rows.Add({3, "Jeff", 35, 13, 0, 57})
    Return dt
End Function