Split rows according to values

[kfloyd3000]

Hi all!

I am new to Power BI and need some help to split into 2+ rows the registries that contain an A in the column2 field. I have searched accross the web and tutorials but havent find an answer for this.

For example, the 4709A4714 must be splitted into 6 rows

The syntaxis always is: Firstvalue A Secondvalue

- Amount of rows to be splitted in = Secondvalue - Firstvalue +1  = 4714 - 4709 +1  = 6

- Value (1st row) = First value = 4709

- Value (2nd row) = First value +1 = 4710

- Value (3rd row) = First value +2 = 4711

- Value (4rd row) = First value +3 = 4712

- Value (5rd row) = First value +4 = 4713

- Value (6rd row) = First value +5 = 4714

Thanks!

[Jakinta · Accepted solution]

Paste this code in new blank query and use/adjust steps to your query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjExN1KK1QEzLKAMC0NHIAEVNrMwgDIszWEMS0cTc0MTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let a=Number.FromText ( Text.BeforeDelimiter([Column2],"A") ), b= Number.FromText ( Text.AfterDelimiter([Column2],"A") ) in if Text.Contains([Column2],"A") then {a..b} else {[Column2]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"