using R to remove and replace numbers

[bvilten]

Hello All,

I have been round the web and not found a solution, hoping you can point me in the right direction. In my data I have a column that contains a string of names:

JD;#1039;#Brian;#160;#Paul;#926;#Jeff;#1789;#Chris;#2919 I need to ";#[0-9]" with a comma.

So expected output is:

JD,Brian,Paul,Jeff,Chris

I have tried all of the transform builtin functions I think of but nothing quite right. Perhaps regex via R?

[LivioLanzo]

Hi @bvilten,

you do not need Regular Expression for this pattern, try:

let
    Source = Text.Combine(
                List.Transform(
                    List.Alternate( Text.Split("JD;#1039;#Brian;#160;#Paul;#926;#Jeff;#1789;#Chris;#2919",";"), 1, 1, 1 ),
                    each Text.Replace(_, "#", "")
                ),
                ","
            )
in
    Source

[bvilten]

Hello and thank you for the reply.

Yes I agree with in that your code does solve the example. However the example is just that. There are thousands of rows within this column all with various names and number combinations.

- Bob

[LivioLanzo]

@bvilten

can you post a more represantitive sample?

[bvilten]

Below is a typical sample of part of the data, The data is FirstName LastName, but I have removed the LastName.

Assigned BRMs	Business Lead(s)	Implementation Team Lead
	Josh;#174;#Judy;#87
Jeff R;#62	Julie;#2826
	Jason;#1235
Jeff R;#62	Tammi;#54
Cyndi T;#323;#Kim F;#187;#Cathy H;#180
Kim F;#187	Dale;#86
Tareq;#2570
	JD;#1039;#Paul;#739
Kirk S;#345
Scott;#966
Chris;#203
Jeff;#62
Jackie E;#525;#Yolanda B;#721;#William W;#142
		David W;#535
Krishna;#2221		Ray G;#1544;#Chandan S;#538;#Paul S;#739;#Chris B;#191
Chris;#629	Tim;#1079;#Chris;#1432
	Mike;#1601	Mike C;#1601;#Michael D;#2081
	JD;#1039;#Brian;#160;#Paul;#926;#Jeff;#1789;#Chris;#2919

[LivioLanzo]

@bvilten

the data presented below follows the same pattern as the previous post: 'Name + ; + Digits', the previous solution should still work as far as I see.

You would need just to concatenate the three columns shown into 1 column and then run the function I posted, have you given it a shot?

[bvilten]

The columns must remain as individual columns so I could apply it 3 times. I'm sorry but I don't understand exactly how to use it. It appears that you have hard coded the "search in" string? 

[LivioLanzo]

@bvilten

try like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText         ("fVJNi8IwEP0robl6aJImaZmb7X6gCIsWZBEPwVYa7AdbdaH/fie17lpWPc7kzZv33mSz8Yg38WbNsQDKdAB0ds46oKHGLvG2k403y/d7sgSquAOeS5sD5SFXv4CewBybGhm4kPcHU1NVFqgMRmPkQRV3dWZJClRwAXRuK/KK5KEGGptT0ZF3V/mjkT8QthJTospQPaBPTZt/oQup/Sciel8JUvoiAvphziVQLaKbje2BrFBjIEdzq11zOgGNlBp7Klp7xKW+eLLTZTYkdtM0u4PNyQvGxyXQz6Y0dWbIFOVwBnRty9KaiqxRasDvkifm22YOIPE+/7fOUVlRGxTHORtelqYjb8goA/wUceE21s6tFOElDFdol0xvzKlhEbt1qrjLKrWVi1BfgU6k4COFC3vAazHls6Eg8aUEurC7wuQlSVxwIbtzl2lrTd3DrzeKuMJv3AfJdPi3l0dsON72Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Assigned BRMs" = _t, #"Business Lead(s)" = _t, #"Implementation Team Lead" = _t]),
    
    fnExtractNames = (name as text) as text =>
        Text.Combine(
                List.Transform(
                    List.Alternate( Text.Split(name,";"), 1, 1, 1 ),
                    each Text.Replace(_, "#", "")
                ),
                ","
            ),
    
    ChangedType = Table.TransformColumnTypes(Source,{{"Assigned BRMs", type text}, {"Business Lead(s)", type text}, {"Implementation Team Lead", type text}}),
    
    AddNamesColumn = Table.AddColumn(
                            ChangedType, 
                            "Names", 
                            each fnExtractNames(
                                        Text.Combine(
                                            List.RemoveMatchingItems({[#"Assigned BRMs"], [#"Business Lead(s)"], [#"Implementation Team Lead"]}, {"", " "}), 
                                            ";"
                                        )
                                 ),
                            type text)
in
    AddNamesColumn

[bvilten]

Thank you that is so very close I will keep messing around with it. What I need to happen is that the cleaned strings remain in the source column. The names can be concatonated olny within their own column.

[bvilten]

@LivioLanzo Thank you for your help.

In the end I could not figure out how to change the source to use a local xlsx file correctly.

I was able to get it done by using the following DAX in a calculated column

CleanedSponsor = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(owssvr[Sponsor],"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),";#;#",", "),";#","")

I would still be interested in pursuing the solution you proposed if you care to spend a little more time on it.

Thanks again

[LivioLanzo]

Hi @bvilten

this only requires a small change, try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText         ("fVJNi8IwEP0robl6aJImaZmb7X6gCIsWZBEPwVYa7AdbdaH/fie17lpWPc7kzZv33mSz8Yg38WbNsQDKdAB0ds46oKHGLvG2k403y/d7sgSquAOeS5sD5SFXv4CewBybGhm4kPcHU1NVFqgMRmPkQRV3dWZJClRwAXRuK/KK5KEGGptT0ZF3V/mjkT8QthJTospQPaBPTZt/oQup/Sciel8JUvoiAvphziVQLaKbje2BrFBjIEdzq11zOgGNlBp7Klp7xKW+eLLTZTYkdtM0u4PNyQvGxyXQz6Y0dWbIFOVwBnRty9KaiqxRasDvkifm22YOIPE+/7fOUVlRGxTHORtelqYjb8goA/wUceE21s6tFOElDFdol0xvzKlhEbt1qrjLKrWVi1BfgU6k4COFC3vAazHls6Eg8aUEurC7wuQlSVxwIbtzl2lrTd3DrzeKuMJv3AfJdPi3l0dsON72Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Assigned BRMs" = _t, #"Business Lead(s)" = _t, #"Implementation Team Lead" = _t]),
    
    fnExtractNames = (name as text) as text =>
        Text.Combine(
                List.Transform(
                    List.Alternate( Text.Split(name,";"), 1, 1, 1 ),
                    each Text.Replace(_, "#", "")
                ),
                ","
            ),
    
    ChangedType = Table.TransformColumnTypes(Source,{{"Assigned BRMs", type text}, {"Business Lead(s)", type text}, {"Implementation Team Lead", type text}}),
    
    AddNamesColumn = Table.TransformColumns(ChangedType, { {"Assigned BRMs",fnExtractNames,type text}, {"Business Lead(s)",fnExtractNames,type text}, {"Implementation Team Lead",fnExtractNames,type text} } )
in
    AddNamesColumn

[bvilten]

Hello,

please explain what this text is (source line) and where it comes from

("fVJNi8IwEP0robl6aJImaZmb7X6gCIsWZBEPwVYa7AdbdaH/fie17lpWPc7kzZv33mSz8Yg38WbNsQDKdAB0ds46oKHGLvG2k403y/d7sgSquAOeS5sD5SFXv4CewBybGhm4kPcHU1NVFqgMRmPkQRV3dWZJClRwAXRuK/KK5KEGGptT0ZF3V/mjkT8QthJTospQPaBPTZt/oQup/Sciel8JUvoiAvphziVQLaKbje2BrFBjIEdzq11zOgGNlBp7Klp7xKW+eLLTZTYkdtM0u4PNyQvGxyXQz6Y0dWbIFOVwBnRty9KaiqxRasDvkifm22YOIPE+/7fOUVlRGxTHORtelqYjb8goA/wUceE21s6tFOElDFdol0xvzKlhEbt1qrjLKrWVi1BfgU6k4COFC3vAazHls6Eg8aUEurC7wuQlSVxwIbtzl2lrTd3DrzeKuMJv3AfJdPi3l0dsON72Bw=="

---

@LivioLanzo wrote:

Hi @bvilten

this only requires a small change, try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText         ("fVJNi8IwEP0robl6aJImaZmb7X6gCIsWZBEPwVYa7AdbdaH/fie17lpWPc7kzZv33mSz8Yg38WbNsQDKdAB0ds46oKHGLvG2k403y/d7sgSquAOeS5sD5SFXv4CewBybGhm4kPcHU1NVFqgMRmPkQRV3dWZJClRwAXRuK/KK5KEGGptT0ZF3V/mjkT8QthJTospQPaBPTZt/oQup/Sciel8JUvoiAvphziVQLaKbje2BrFBjIEdzq11zOgGNlBp7Klp7xKW+eLLTZTYkdtM0u4PNyQvGxyXQz6Y0dWbIFOVwBnRty9KaiqxRasDvkifm22YOIPE+/7fOUVlRGxTHORtelqYjb8goA/wUceE21s6tFOElDFdol0xvzKlhEbt1qrjLKrWVi1BfgU6k4COFC3vAazHls6Eg8aUEurC7wuQlSVxwIbtzl2lrTd3DrzeKuMJv3AfJdPi3l0dsON72Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Assigned BRMs" = _t, #"Business Lead(s)" = _t, #"Implementation Team Lead" = _t]),
    
    fnExtractNames = (name as text) as text =>
        Text.Combine(
                List.Transform(
                    List.Alternate( Text.Split(name,";"), 1, 1, 1 ),
                    each Text.Replace(_, "#", "")
                ),
                ","
            ),
    
    ChangedType = Table.TransformColumnTypes(Source,{{"Assigned BRMs", type text}, {"Business Lead(s)", type text}, {"Implementation Team Lead", type text}}),
    
    AddNamesColumn = Table.TransformColumns(ChangedType, { {"Assigned BRMs",fnExtractNames,type text}, {"Business Lead(s)",fnExtractNames,type text}, {"Implementation Team Lead",fnExtractNames,type text} } )
in
    AddNamesColumn

---