Expand the values for dimension table

[v_mark]

I do have a dimension table that has this type of format.

value	category	rating_type
{1,3}	Very Poor	Service
{4,8}	Below Expectation	Service
{9,15}	Meets Expectation	Service
{16,24}	Exceeds Expectation	Service
{25}	Very Good	Service

Is there a way in PQ to expand these values into... Not sure if there is a way to do it by using the native buttons inside PQ w/o writing any M code. 

value	category	rating_type
1	Very Poor	Service
2	Very Poor	Service
3	Very Poor	Service
4	Below Expectation	Service
5	Below Expectation	Service
6	Below Expectation	Service
7	Below Expectation	Service
8	Below Expectation	Service
9	Meets Expectation	Service
10	Meets Expectation	Service
11	Meets Expectation	Service
12	Meets Expectation	Service
13	Meets Expectation	Service
14	Meets Expectation	Service
15	Meets Expectation	Service
16	Exceeds Expectation	Service
17	Exceeds Expectation	Service
18	Exceeds Expectation	Service
19	Exceeds Expectation	Service
20	Exceeds Expectation	Service
21	Exceeds Expectation	Service
22	Exceeds Expectation	Service
23	Exceeds Expectation	Service
24	Exceeds Expectation	Service
25	Very Good	Service

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqjbUMa5V0lEKSy2qVAjIzy8CsoNTi8oyk1OVYnWA8iY6FiB5p9Sc/HIF14qC1OSSxJLM/Dx0dZY6hqYghb6pqSXF+BQamukYmYBUulYkp6am4FVrZAp3m3t+fgqydCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, category = _t, rating_type = _t]),
    Custom1 = Table.ReplaceValue(Source,each [value], each {Number.From(Text.Split(Text.Select([value],{"0".."9",","}),","){0})..try Number.From(Text.Split(Text.Select([value],{"0".."9",","}),","){1}) otherwise Number.From(Text.Split(Text.Select([value],{"0".."9",","}),","){0})},    Replacer.ReplaceValue,{"value"}),
    #"Expanded value" = Table.ExpandListColumn(Custom1, "value")
in
    #"Expanded value"

[BA_Pete · Accepted solution]

Hi @v_mark ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqjbUMa5V0lEKSy2qVAjIzy8CsoNTi8oyk1OVYnWA8iY6FiB5p9Sc/HIF14qC1OSSxJLM/Dx0dZY6hqYghb6pqSXF+BQamukYmYBUulYkp6am4FVrZAp3m3t+fgqydCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, category = _t, rating_type = _t]),
    extTextBetween = Table.TransformColumns(Source, {{"value", each Text.BetweenDelimiters(_, "{", "}"), type text}}),
    splitByDelim = Table.SplitColumn(extTextBetween, "value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"value.1", "value.2"}),
    addNumberList = Table.AddColumn(splitByDelim, "numberList", each
        if [value.2] = null then {Number.From([value.1])..Number.From([value.1])}
        else {Number.From([value.1])..Number.From([value.2])}
    ),
    expandToNewRows = Table.ExpandListColumn(addNumberList, "numberList")
in
    expandToNewRows

I get the following output:

Pete