Forum Discussion
Split Column by certain amount of characters
Hi there,
I got the following data I wann split or extract in power query.
IS.DJ CHINA OFFS.50 U.ETF A0F5UE USD 0,117987
E.ON SE NA O.N. ENAG99 EUR 0,51
BARRICK GOLD CORP. 870450 USD 0,10
MOWI ASA NK 7,5 924848 NOK 1,70
I wann split before and after the bold marked character string. Its always a string (despite of numbers) and always has a length of 6.
I need that 6 character length information in a seperate column.
Big Thanks for any advice - I tried a lot so far.
Hi Janni20, I've created 2 versions of extract for you. I'd prefer v2.
v1 logic: it extracts first 6 char string in text
v2 logic: you can specify delimiter (currencies). It will check for currency position and extract 6 char word before this delimiter. Add more currencies in v2_Currencies step if necessary.
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcxBDsIgFEXRrbx0TH7AQIAhFqiIggFJB033vw3bOj/3btuUOvkn5kcqDjXGTopjUPhGOB7VCBjdgzMhtDV62tk2BaoFPeAMqBBCcYu1CKMdTonL3F1rac5Y6stjru1DMJrL8/3f8Yu965rgukPJ0EzB3qSRBqVmCKYPs/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), v1_Extract = Table.AddColumn(Source, "Extract v1", each List.Select(Text.Split([Column1], " "), (x)=> Text.Length(x) = 6){0}?, type text), v2_Currencies = {"USD", "EUR", "NOK"}, v2_Extract = Table.AddColumn(v1_Extract, "Extract v2", each [ a = List.Select(List.Transform(v2_Currencies, (x)=> Text.PositionOf([Column1], " " & x)-6), (y)=> y > 0), b = List.Select(List.Transform(a, (x)=> Text.BeforeDelimiter(Text.Range([Column1], x), " ")), (y)=> Text.Length(y) = 6){0}? ][b], type text) in v2_Extract
4 Replies
Hi Janni20, I've created 2 versions of extract for you. I'd prefer v2.
v1 logic: it extracts first 6 char string in text
v2 logic: you can specify delimiter (currencies). It will check for currency position and extract 6 char word before this delimiter. Add more currencies in v2_Currencies step if necessary.
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcxBDsIgFEXRrbx0TH7AQIAhFqiIggFJB033vw3bOj/3btuUOvkn5kcqDjXGTopjUPhGOB7VCBjdgzMhtDV62tk2BaoFPeAMqBBCcYu1CKMdTonL3F1rac5Y6stjru1DMJrL8/3f8Yu965rgukPJ0EzB3qSRBqVmCKYPs/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), v1_Extract = Table.AddColumn(Source, "Extract v1", each List.Select(Text.Split([Column1], " "), (x)=> Text.Length(x) = 6){0}?, type text), v2_Currencies = {"USD", "EUR", "NOK"}, v2_Extract = Table.AddColumn(v1_Extract, "Extract v2", each [ a = List.Select(List.Transform(v2_Currencies, (x)=> Text.PositionOf([Column1], " " & x)-6), (y)=> y > 0), b = List.Select(List.Transform(a, (x)=> Text.BeforeDelimiter(Text.Range([Column1], x), " ")), (y)=> Text.Length(y) = 6){0}? ][b], type text) in v2_ExtractYou can just solve this problem by adding two simple custom column based on Text.AfterDelimiter and Text.BeforDelimter
To see the solution, copy and past the below code into advance editor.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcxBDsIgFEXRrbx0TH7AQIAhFqiIggFJB033vw3bOj/3btuUOvkn5kcqDjXGTopjUPhGOB7VCBjdgzMhtDV62tk2BaoFPeAMqBBCcYu1CKMdTonL3F1rac5Y6stjru1DMJrL8/3f8Yu965rgukPJ0EzB3qSRBqVmCKYPs/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Added Custom" = Table.AddColumn(Source, "Before", each Text.BeforeDelimiter([Column1]," ",{2,1})), Custom1 = Table.AddColumn(#"Added Custom", "After", each Text.AfterDelimiter([Column1]," ",{1,1})) in Custom1Thanks Omid and dufoq3 for your help!
You're welcome, enjoy 😉
