Hello,
In a power query I need to take a column that summarizes weeks in a roster ("weken" in Dutch) as such: "1-7, 9-11, 13" to mean weeks 1 to 7, 9 to 11 and week 13. (see snipit_1 attached) In order to work with this data I need to unpivot this column for sure. But the first step is to get from this summarized notation to a set of columns for the different weeks that I can then unpivot. (At least, that's how I thought of doing it).
I started by splitting the columns based on the comma, so that in the example I would end up with three columns (see snipit_2 attached). Next I want to convert 1-7 to 1,2,3,4,5,6,7. I found a VBA solution to it, but I want to be able to do it in M so I can include it in the query and have it run automatically when I upload a new version of the roster.
Snipit_1:
snipit_2:
Can this be done? Other approaches that may work?
Kind regards,
Karin
Hi Karin,
Do you want 1,2,3,4,5,6,7 in one cell, or split over 7 columns? Also, how would you handle 1-13?
Please upload a sample Excel file with an extract of your data with the original data and the desired result so we can see it in context and use it to provide you with a solution.
Thanks,
Mynda
