Forum Discussion
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 |
| {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 |
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 expandToNewRowsI get the following output:
Pete
2 Replies
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 expandToNewRowsI get the following output:
Pete
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"
