Forum Discussion

v_mark's avatar
v_mark
Helper V
4 years ago
Solved

Expand the values for dimension table

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 ...
Need Help
Show and Tell
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"