March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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!
Solved! Go to 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"
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.