Remove space between first name and last name

[Sansudhi8]

Dear All,

Kindly look into the attached PBI . It contains charge for provider by unique ID. I want to create a sclicer for provider. There are some items which have space between first name and last name, but it should be a one name. 

Could you please help me to remove that space for all.

PBI

Thank you.

Sansudhi. 

[mussaenda · Accepted solution]

Hi @Sansudhi8 

This the result you need?

try this in m query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdNNCsJADAXgq8jgskj+ZvKyVOiiWFyou9L7X8P2Aibb8tE8kjfb1oSsh482te97eT3Xebosn/tjXo8vV7Zxa/t0qmFmkqugyr+COxeUiabKiSif6MRuBaUauVL2gjJQvq9T9Vx1Gvm+fEjhQn4sH7lyNa+oUZgI1UL6EKGCUq0oaJ4ex73zTYAi8vRg5sJEVuTXBgN5v8Chee8hgrz3pyqklx75e4SMQicgLn/T7z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Uniq ID" = _t, Provider = _t, TotalCharge = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Uniq ID", Int64.Type}, {"Provider", type text}, {"TotalCharge", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BeforeDelimiter([Provider], " ") &
Text.AfterDelimiter([Provider], " "))
in
    #"Added Custom"

[Sansudhi8]

Hi @mussaenda ,

Could you please add this to my PBI. 

Thank you. 

Sansudhi. 

[mussaenda]

Hi @Sansudhi8 

Open a new Power BI,

Copy the code and paste to the advanced query editor. Test it then from there you can do to your Power BI report.

[Sansudhi8]

Dear @mussaenda ,

Above given code is perfectly working with provider names such as " OSWALT, NICOLE" and "PEDDI, JAYA". 

But it's not working for provider names like " SHANBHAG PAI, SANGEETH" and "SHANBHAG PAI,SANGEETH". 

Provider names I have.

SHANBHAG PAI, SANGEETH

SHANBHAG PAI,SANGEETH

I need only "SHANBHAG PAI,SANGEETH"

Could you please help me to sort out this as well. 

Thank you very much for your time. 

Sansudhi. 

[mussaenda]

Hi @Sansudhi8,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldJNasNADAXgqxjTpQn6m5G0nICJTYIpdXYh979G7S67mZft8DGSnvR6jUJW0us4jc+fdbs/5mlY93adH8fLF1u9jO/pVNXMpK+SkL+SCwPKRLvKiahf0YndAKWafaXsgLKgfl6nKn1VqPbz8irAhvwIP/rK1RxRFagYqmf3+9K269Juw3dbp2Fv222en8t/nCKEY9UPcKijOI5bCBxnwgMGM+NtsEbBcUTiOLXCWCTsE4wPKCUVx/Xv3EDsggz4/gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Uniq ID" = _t, Provider = _t, TotalCharge = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Uniq ID", Int64.Type}, {"Provider", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BeforeDelimiter([Provider], ",") & "," &
Text.AfterDelimiter([Provider], ", "))
in
    #"Added Custom"

try this

[Sansudhi8]

Dear @mussaenda ,

Thanks for the reply. 

Now its doesn't work with the correct names. Kindly look into this.

I have list of valid and invalid items in relavent column. kindly look into the below table. It will give you clear idea. 

Could you please help me again to correct invalid names. 

I just want to remove space next to "," only. 

Provider Name	Status	Correct name
GANEM-PEREZ,ANA	Valid
JOUKHADAR, RENE	Invalid	JOUKHADAR,RENE
JOUKHADAR,RENE	Valid
KATARIA, RAHUL	Invalid	KATARIA,RAHUL
MAIDANA PAZ, CLAUDIA	Invalid	MAIDANA PAZ,CLAUDIA
NAVARRO,HEDSNA HOPE	Valid
OSSAI,NDUKA-OBI FRANCIS	Valid
OSWALT, NICOLE	Invalid	OSWALT,NICOLE
OSWALT,NICOLE	Valid
PEDDI, JAYA	Invalid	PEDDI,JAYA
PEREZ GOMEZ, CRISTINA	Invalid	PEREZ GOMEZ,CRISTINA
PRICE, KELLY	Invalid	PRICE,KELLY
RIVERA CRESPO,LUIS	Valid
RIVERA GUERRERO, JOSE	Invalid	RIVERA GUERRERO,JOSE
ROBINSON, MEGHAN	Invalid	ROBINSON,MEGHAN
ROBINSON,MEGHAN	Valid
ROCHA, HEDSNA HOPE	Invalid	ROCHA,HEDSNA HOPE
SARETTE,WILLIAM	Valid
SHANBHAG PAI, SANGEETH	Invalid	SHANBHAG PAI,SANGEETH
SHANBHAG PAI,SANGEETH	Valid
WOODS, KRISTI	Invalid	WOODS,KRISTI
WOODS,KRISTI	Valid

Sansudhi. 

[mussaenda]

This will 100% but is not the short code.

Try and let me know

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5dTBCoMwDAbgVxHZUUabxCY5diBTJjKmt7H3f43pjrLR3/Ou5aPNH8r/fNYUpHVNdVMvj2G6jV1TDXO+dON6coqSzvWr2VQSESorD8hdHtsIKCEuKg0hlF/8h4waokpZrYNZWSmLIiqV96XGvG1i7vN06fO1uuehqeY8Xbtu6ffYiQKOmQ9gY0Wxrds0HLvDAS3GiI8R2VocmzmOnROMiUyOYDwgtc44Tp/vBmKlAwG/4B92XyVQk0BFAvUIVCNQi0AlskevNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Uniq ID" = _t, Provider = _t, TotalCharge = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Uniq ID", Int64.Type}, {"Provider", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Provider", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Provider.1", "Provider.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Provider.1", Text.Trim, type text}, {"Provider.2", Text.Trim, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Trimmed Text",{"Provider.1", "Provider.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Custom")
in
    #"Merged Columns"

[Sansudhi8]

Hi @mussaenda ,

Thank you very much. Yes this is what i need. 

Sansudhi. 

[amitchandak]

@Sansudhi8 , In M you can use

https://docs.microsoft.com/en-us/powerquery-m/text-trim

https://docs.microsoft.com/en-us/powerquery-m/text-remove

[Sansudhi8]

Dear @amitchandak ,

I couldn't fix it. Could you please help me further. 

Sansudhi.