Log In
Register
Home
January 5, 2022
Offline
Hi All, This seems pretty challenging and not sure if its doable but I know this is the place to bring up this issue.
I am not sure I can change the source data so this is what I have to work with. I have a file that I pull from a vendor which has 11 columns indicating schedule details. Each section is for a specific person. I would like to come up with some sort of a process that I can paste an updated data pull each month and then have PowerQuery create a new column with the persons name on each row, related to that data set. So if you open up my sample file the first section is related to Peck, Stevens on line 7. I would like to add a column with a header name and then have Peck Stevens on each row starting on line 9 until it reaches the next data section which is Dean, James. This would be repeated until all data set has a name on each row related to their respective section.
Moderators
January 31, 2022
Offline
The attached file contains what could be the beginning of a solution, though it's based on a the first 143 rows only. It would work the same on 2.5K rows.
First I named the data range "assignments" and connected to it with PQ.
Add an index column and then a custom column taking the Name from Column1 where the value on the next row equals "Date". Then fill the name down.
You didn't specify what you want to do next. The data isn't really organised very well. It still has empty row in it, repeating header rows and sub-totals. Probably something you would want to get rid of in case you intend to do any further analysis. Up to you to clarify.
January 5, 2022
Offline
Thank you for this. Your skills are clearly way beyond mine as I would have never figured that out.
I agree the output is not optimal and unfortunately the person I am working with does not believe the vendor is flexible to get it in a tabular format I am suggesting. When I was shared the actual file that gets downloaded from the system it has 2630 lines. I doubt that it will get over 3,000 lines but that is a variable. I am attaching a sample of what I want the final output to look like so you can see. In the screenshot I did some formatting where I removed some lines and I promoted a header and cleaned out some of the empty and other column headers which are duplicative and needed to be filtered out so its a clean long running tabular data set. When I filtered out the Date in the first column, some of the lines obviously above had the persons name so the way I got my date only is just by manually filtering out the persons name. Maybe there is a better function that states only keep "date like" data fields. As the files will get downloaded and may have new names that may come up so my manual filtering may not be the best option long term. Hopefully this makes sense but please ask questions so I can help with assisting as best as I can.
1 Guest(s)
