The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnUMjgxwDPLWNzLSNTDVNTJWitVBiGIV1EKIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.PositionOf([Data],"/")>=0 and Value.Is(Number.FromText(Text.At([Data],Text.PositionOf([Data],"/")+1)), type number) then Text.Replace([Data],"/","~%^/") else [Data]),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("~%^", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Data"})
in
#"Removed Columns"
I think the simplest thing to do would be to split on the "/" and then add it back if you need it.
Sample query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnUMjgxwDPLWNzLSNTDVNTJWitVBiBoZgbmO+gZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Col1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Before/", "/After"}),
#"Prepend Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"/After", each "/" & _, type text}})
in
#"Prepend Text"
Note: This doesn't check that "/" is followed by a digit but I can't tell from a single example whether that matters or not for your real data.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnUMjgxwDPLWNzLSNTDVNTJWitVBiGIV1EKIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.PositionOf([Data],"/")>=0 and Value.Is(Number.FromText(Text.At([Data],Text.PositionOf([Data],"/")+1)), type number) then Text.Replace([Data],"/","~%^/") else [Data]),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("~%^", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Data"})
in
#"Removed Columns"