extracting numbers from a serie

[jwi1]

Dear all,

I am looking for a solution for the following.

Below are a few container number series:

BMOU	977351	977360
BMOU	977551	977559
BMOU	983321	983321
BMOU	983322	983322
BMOU	985193	985210

I am looking for a formula which gives me the unique numbers in each range.

The list should then look as follows:

BMOU	977351
BMOU	977352
BMOU	977353
BMOU	977354
BMOU	977355
BMOU	977356
BMOU	977357
BMOU	977358
BMOU	977359
BMOU	977360
BMOU	977551
BMOU	977552
BMOU	977553
BMOU	977554
BMOU	977555
BMOU	977556
BMOU	977557
BMOU	977558
BMOU	977559
BMOU	983321
BMOU	983322
BMOU	985193
BMOU	985194
BMOU	985195
BMOU	985196
BMOU	985197
BMOU	985198
BMOU	985199
BMOU	985200
BMOU	985201
BMOU	985202
BMOU	985203
BMOU	985204
BMOU	985205
BMOU	985206
BMOU	985207
BMOU	985208
BMOU	985209
BMOU	985210

Thanks upfront for your help!

regards,

John

[amitchandak · Accepted solution]

@jwi1 , If you have three columns

create a new column in power query

COl = List.Numbers([Column2],[Column3] -[Column2],1)

Then expand the list into rows

[mahoneypat · Accepted solution]

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

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL1D1XSUbI0Nzc2NYQyzAyUYnWQpUxhUqamlihSFsbGRoYIBrqUEYKBImVqaGkMYRgZAu2KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Column2] .. [Column3]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Column1", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

Pat

[onurbmiguel_ · Accepted solution]

Hello 

Is my sugestion in Power Query: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL1D7U0Nzc2NQSRZgZKsTpwQVOwoKmpJULQwtjYyBBCogoaQUgkQaBmYyBpZAg0MxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}}),
    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Serial Number], 4), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "Prefix"}}),
    #"Inserted Text Range" = Table.AddColumn(#"Renamed Columns", "Text Range", each Text.Middle([Serial Number], 4, 6), type text),
    #"Inserted Last Characters" = Table.AddColumn(#"Inserted Text Range", "Last Characters", each Text.End([Serial Number], 6), type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Last Characters",{{"Text Range", "Start"}, {"Last Characters", "End"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Start", Int64.Type}, {"End", Int64.Type}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Changed Type1", "number rows", each [End] - [Start], Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Subtraction", "individual number", each List.Numbers([Start],[number rows]+1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "individual number"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each [Prefix] & Text.From([individual number])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Prefix", "Start", "End", "number rows", "individual number"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Individual Number"}})
in
    #"Renamed Columns2"

image: 

PBIX:

https://1drv.ms/u/s!AkcWVrMFkXs1hvoY1WMhzBF2qcWemw?e=uPurjn

Any question ask. 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Best Regards
BC

[jwi1]

Hi @onurbmiguel_ ,

Problem solved, thanks!

John

[mahoneypat · Accepted solution]

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

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL1D1XSUbI0Nzc2NYQyzAyUYnWQpUxhUqamlihSFsbGRoYIBrqUEYKBImVqaGkMYRgZAu2KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Column2] .. [Column3]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Column1", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

Pat

[amitchandak · Accepted solution]

@jwi1 , If you have three columns

create a new column in power query

COl = List.Numbers([Column2],[Column3] -[Column2],1)

Then expand the list into rows