Forum Discussion

kfloyd3000's avatar
kfloyd3000
Regular Visitor
3 years ago
Solved

Split rows according to values

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!

 

Need Help
Tips and Tricks
  • Jakinta's avatar
    Jakinta
    3 years ago

    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"

     

     

1 Reply

  • Jakinta's avatar
    Jakinta
    Solution Sage
    3 years ago

    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"