Expand Tier Number to Tier Number List?

[Anonymous]

Hello everyone,

I have a table like this

3	Tier 1
6	Tier 2
10	Tier 3
15	Tier 4

How can I expand the table into this:

1	Tier 1
2	Tier 1
3	Tier 1
4	Tier 2
5	Tier 2
6	Tier 2
7	Tier 3
8	Tier 3
9	Tier 3
10	Tier 3
11	Tier 4

Thanks in advance!

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUQrJTC1SMFSK1YlWMoNxjcBcQwMY3xjCN4XxTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Till = _t, Tier = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "From", each try #"Added Index" [Till] { [Index]  - 1 } otherwise "1", type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [From]="1" then 1 else Number.FromText([From])+1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Till", Int64.Type}, {"Custom", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.1", each {[Custom]..[Till]}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom2", "Custom.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"From", "Index", "Till", "Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.1", "Tier"})
in
    #"Reordered Columns"

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may apply the following transformations in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUQrJTC1SMFSK1YlWMoNxjcBcQwMY3xjCN4XxTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
    Custom1 = Table.TransformColumns(
     #"Changed Type",
     {"Column1",each 
     let x=List.Max(List.Select(#"Changed Type"[Column1],(x)=>x<_)),
     start=if x=null then 1 else x+1,
     end=_ 
     in 
     List.Generate(
         ()=>start,
         each _<=end,
         each _+1
     )
     }
),
    #"Expanded Column1" = Table.ExpandListColumn(Custom1, "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1", Int64.Type}})
in
    #"Changed Type1"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUQrJTC1SMFSK1YlWMoNxjcBcQwMY3xjCN4XxTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Till = _t, Tier = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "From", each try #"Added Index" [Till] { [Index]  - 1 } otherwise "1", type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [From]="1" then 1 else Number.FromText([From])+1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Till", Int64.Type}, {"Custom", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom.1", each {[Custom]..[Till]}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom2", "Custom.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"From", "Index", "Till", "Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.1", "Tier"})
in
    #"Reordered Columns"

[HotChilli]

In Power Query, create a new blank query.

Paste this :

= {1..15}

This will create a list of 1-15.

Convert it to a table (from menu).

Make sure data type is whole number.

Then Merge this table with the original table (using Left join, on the number column).

You'll get a 2 column table (1st column is number 1-15, 2nd column is 'table').

Expand the the second column to return the text with "tier..".

Sort the first column (to ensure it is ascending).

Use 'Fill Up' on the second column.

Good luck.

[mahoneypat]

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUQrJTC1SMFSK1YlWMoNxjcBcQwMY3xjCN4XxTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Tier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Tier", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Repeat({[Tier]}, try (if [Number] = List.Max(#"Added Index"[Number]) then 1 else [Number] - #"Added Index"{[Index]-2}[Number]) otherwise [Number] - 0)),
    Custom1 = List.Combine(#"Added Custom"[Custom]),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index1" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index1",{"Index", "Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1", type text}})
in
    #"Changed Type1"

Pat