Generate series of numbers based on other table values

[Anonymous]

Hi All,

I am having a data like below,

Label	Start	End
Car	1	3
Bike	1	5

My expected result should be,

Label	Value
Car	1
Car	2
Car	3
Bike	1
Bike	2
Bike	3
Bike	4
Bike	5

Could anybody help me to get the expected table?

Thanks,

Deepak

[v-lili6-msft · Accepted solution]

hello @deepaknarayank

you can use this simple formula to create a new table

New table =
SELECTCOLUMNS (
    FILTER (
        GENERATE (
            'Table',
            GENERATESERIES ( MIN ( 'Table'[Start] ), MAX ( 'Table'[End] ), 1 )
        ),
        [Value] >= [Start]
            && [Value] <= [End]
    ),
    "Label", [Label],
    "Value", [Value]
)

result:

and here is the sample pbix file, please try it.

Best regards

Lin

[Anonymous]

Hi Lin,

Thank you so much for your solution..

This works fine..

Thanks & regards,

Deepak

[FrankAT]

Hi,

open Power Query and follow these steps:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMgRiY6VYnWglp8zsVKiAqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Label = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}, {"Start", Int64.Type}, {"End", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Series", each {[Start]..[End]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Label", "Series"}),
    #"Expanded Series" = Table.ExpandListColumn(#"Removed Other Columns", "Series")
in
    #"Expanded Series"

Regards FrankAT