Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
v_mark
Helper V
Helper V

Expand the values for dimension table

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

 

valuecategoryrating_type
{1,3}Very PoorService
{4,8}Below ExpectationService
{9,15}Meets ExpectationService
{16,24}Exceeds ExpectationService
{25}Very GoodService

 

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. 

 

valuecategoryrating_type
1Very PoorService
2Very PoorService
3Very PoorService
4Below ExpectationService
5Below ExpectationService
6Below ExpectationService
7Below ExpectationService
8Below ExpectationService
9Meets ExpectationService
10Meets ExpectationService
11Meets ExpectationService
12Meets ExpectationService
13Meets ExpectationService
14Meets ExpectationService
15Meets ExpectationService
16Exceeds ExpectationService
17Exceeds ExpectationService
18Exceeds ExpectationService
19Exceeds ExpectationService
20Exceeds ExpectationService
21Exceeds ExpectationService
22Exceeds ExpectationService
23Exceeds ExpectationService
24Exceeds ExpectationService
25Very GoodService
1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1651658445114.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

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
Super User
Super User

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:

BA_Pete_0-1651658445114.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors