Forum Discussion
split long string by a varies of string delimiter
Hi!
The problem seemed to be so complex, and there were 14K records. A kind of solution was found, some python code was translated into M. Thank you all for the time and energy to find solutions.
Regards
Dori
Hello,
We have managed to create a kind find of solution to split long text in a table of 80K rows and also have a table with a list from World Bank with all possible countries in the world. Institutes in cells are separated mostly with comma, but sometimes only with 'and'. The perfect solution would lead to a possibility to count the institutes in the cell, or better to split the institutes by a delimiter, f.e. | (pipe)
The examples:
example1: two institute, both from the same country
Sántha G., Faculty of Health Sciences, Doctoral School of Health Sciences, University of Pécs, Pécs, Hungary, 'Juhász Gyula' Faculty of Education, University of Szeged, Szeged, Hungary
example2: two institutes, one of them is from a country wich name contains two words (United States)
Longobardi S., Department of Internal Medicine, HCA Healthcare, USF Morsani College of Medicine GME, HCA Florida Blake Hospital, Bradenton, FL, United States, Department of Internal Medicine and Hematology, Semmelweis University Alumnus, Budapest, Hungary
example3: three institutes, one is from Gibraltar, which word is also a name of a city and a country, but must be count only once
Demetrovics Z., Institute of Psychology, ELTE Eötvös Loránd University, Budapest, Hungary, Centre of Excellence in Responsible Gaming, University of Gibraltar, Gibraltar, College of Education, Psychology and Social Work, Flinders University, Adelaide, SA, Australia
example4: 3 institutes, two of them from a country which name's contains the word: 'and'
Đogo M., University of East Sarajevo, Faculty of Economics Pale, Bosnia and Herzegovina; Gligorić D., University of Banja Luka, Faculty of Economics, Bosnia and Herzegovina; Berecz M., Ministry of Foreign Affairs of Hungary, Budapest, Hungary
example5: two institutes separated by 'and'
Kiss L., Department of Organic and Medicinal Chemistry, Faculty of Pharmacy, University of Pécs, Honvéd Street 1, Pécs, H-7624, Hungary and János Szentágothai Research Center, University of Pécs, Ifjúság Street 20, Pécs, H-7624, Hungary
Thanks for trying!
Hi vizdor,
The only logic I can see based on your examples is counting occurrences or splitting the text by this dynamic substring: ", [countryName]", i.e.
You can transform your list of countries to this format:
and then use it to replace the string with some delimiter, i.e. "|". Final step should be splitting the text or just counting the occurrences:
To test the solution use queries below:
countries table
// countries
let
Source = List.Transform ({"Hungary", "United States", "Gibraltar", "Australia", "Bosnia and Herzegovina"}, each ", " & _)
in
Source
// tbl4
let
//sample data
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVTLUttAEPyVKZ8VilCpcMjJb0PsiitKKlUhHIbVIA+sdly7KyfmB3LNL3DkBzjkKj4sI9mADXZO0m5punt6enR21kqrWxdnCMODBAZoShuXIJcwIrRxBqlhcoZCAj0xUTxavZqJ2J3ffHW8IB94BTGt7oxerh+j0uXolwn8KA8Pj45Py1l1G25guCwtrq426ftZaTCyuJeg6Q3llCVPzzVs6zw5a43F5XKBPmNItZsezdHHglysC09cJO9U/4QyNuxIa7vtdQ8GvZ6/pgOYiA/oGLpirTLUlY8FMJz0V0UDK54zhI7Fa4KRhDlHtAl0PGZKV6sejBvlkTJII8bGwf/rAXSZyikwipVcjUqpKMj+JA6bHrRtWbhS4TplhnMKcduDHhUUvSzYBPiuJpy4EDmWselkGpY6vBV6f/ylD/3qPi6q+wBj8ZqDbINoB0ECXRXvG6j+L0PqkM4d2MFnCnNxgS+suoQFu/zl3IZ8oeGJ6JPN1w2XNyb+LLPxJBXD6tM38dfqq2WXKeyW0nZGFjnTEaZtPZUhKgFjY8jDH8kFJgcvBfUxREjR4xUtZCv6fSNOitrAKVrF7EhwjOvxeM2duuvwAwwt55qDh9/Qe4XeQXeFMC6vcTf0ftQOeTI3jeAJO9ZWmsqBeOLcQfvyElnbr9fvcSq7k/CRg8711R588rnm2zTE6+ipud0ZFQ3XltzpDH2BZrlns0fiFtVdHXBPFOHt866/OX5/9O5JTsN1qvmSUO+ti9VtLvrP4To3hN7MmmCR38NzcnlV/Q1a9Mh0dLiPqnV+/g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t]),
//transformations start here
ReplaceCountries = List.Accumulate(List.Buffer(countries), Source, (current, state) => Table.TransformColumns(current, {{"text", each Text.Replace(_, state, "|") }}) ),
#"Added count" = Table.AddColumn(ReplaceCountries, "count", each List.Count(Text.PositionOf([text], "|", Occurrence.All)))
in
#"Added count"