Enumerate set of values from hyphen or dash

[jmg80525]

Greetings:

Thought I would query the forum before I took a stab at it. Have a spreadsheet where a continuum of values are denoted with the starting and ending value separated by a dash or a hyphen. For instance, 1-3 would indicate the set of the values 1,2,3. Looking for some M snippet that would calculate the the delimited set of values. If nothing else a couple of clues on approach or pattern would be appreciated.

TIA.

 

[Ken Puls]

Let's pretend that your column is called "value"
Split the Value column by the delimiter
Add a new Custom Column using the following function
={[Value.1]..[Value.2]}
Click the Expand icon in the top right of the new column and choose to Extract Values...
Choose the comma as your delimiter
Delete the original two Values columns

If you don't have all those commands, it may be time to update your Power Query.

M Code (for the Advanced Editor)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", Int64.Type}, {"Value.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "List", each {[Value.1]..[Value.2]}),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"