Question on Data Cleaning/Pre-Work

I have a large wide data set (hundreds of columns and each row is a new batch or lot) with data from manufacturing runs and I am trying to set up the data in the most usable way. This is data over several days where many types of tests are run, I have basically the same data across several days for many different assays that come in different types like text, percentages, very small values (small decimals), very large values (billions).

I have been trying to just use the data from the source as is, but I am finding it much more difficult to use than I've seen in other similar programs (Tableau and Spotfire). For example, if I want to see how a particular assay shows across the entire manufacturing for each lot, I have no idea how to group all of the column from that particular assay so that they can be shown easily in a visualization.

So my question is, how can I organize the data in a way that makes this possible by either using the current data table in it's current shape/format or by transforming the data? My initial thoughts were:

1. Use the data as is and figure out how to group columns in a meaningful way that allows me to compare individual batches as well as create dynamic control charts. I have no idea how to do this at this point.

2. Create several tables including a dimension table with 1 row per lot and all of the text/date columns and then several unpivoted tables for each assay. My concern here is that I wouldn't really be able to create dynamic visualizations and that the columns may eventually change and I'm not sure how to go about ensuring issues don't arise.

Edit:

Here is an example of the first output I'd like to see. This is just a control chart based on one of the measures. What I've done in the past is create something like this below created a Parameter with Parameter Values (this is from Tableau) where I could have the y axis (along with the reference lines, moving average, and axis scale) change based on a drop down menu to any particular measure that I wanted.

I also would like something like this where it's a line chart of different timepoints for the same measure and I have all of the lots plotted out, but only the one I have selected shows as a color and the rest are grey and have increased transparency. I did this in Tableau before but recreated it here in excel.

There are a lot more visualizations I will be working on, but I thought these two might give me a good base to work from. Here is the fake data I generated (I had to delete some columns and rows to make the character limit for the post):