Convert columns summarizing a week range to separate columns using M-language | Power Pivot | Excel Forum

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