CSV to Report - How to remove unneeded columns and rows

[Cbrickner]

Screenshot examples made up below. 1st one is of the CSV downloaded from web portal, 2nd one is after manually removing rows and columns to see needed data. Is there a formula or quicker way to get this to convert properly? Pull this down daily, sometimes multiple times a day. There is data in different rows vs columns that is needed so we cant just use the filter as it stands. I have added data to rows 1 & 5 in column B in order to filter out blanks but then there are columns to deal with.

Any help would be appreciated.

 

[jdellasala]

I never open a CSV file directly to work with since learning Power Query. It can easily make short work of cleaning up the CSV, and if you're downloading the CSV on a regular basis and save it to the same filename, doing it once you're set up will take one click! For great YouTube playlists on the subject check here and here. I particularly like the later playlist as each video has a before and after workbook to follow. Don't be intimidated by the size of the lists. There is TONS to learn about PQ, but it's interface is very simple and powerful, and you should be able to do what you need in no time - and save HOURS of work in the long run. And you can always get help in the Power Tools forum!

 

[Cbrickner]

Thank you, getting a better layout by columns but rows are my problem, have dates in columns but data needs in others and not sure how to filter. Saved the PQ before doing filters and now cant find.

 

[datatronics505]

Thank you, getting a better layout by columns but rows are my problem, have dates in columns but data needs in others and not sure how to filter. Saved the PQ before doing filters and now cant find.

Hi,
Fill Down/Fill Up will help you deal with row data for those rows that are empty before the new set starts, like for example for a given date you have several rows empty before the new date is given. If the top row has data, Fill Down. If it's empty, Fill Up.
Also play with Pivot, Unpivot and Transpose options for columns and see how results correspond to what you want to get. Power Query works on columns, rows aren't its forte. If you really need to work on rows, there's always the Insert Index Column option and you use that column as a compass in transforming row data. There's a decent amount of scrubbing to be done on that table you showed, but the great thing about Power Query is that you'll have to do it only once, provided new data comes in a consistent format.
Before learning Power Query I gave up on using Excel for text pre-processing and leaned on command line text processing utilities to insert delimiters in CSV files I planned to later load into Excel.