Forum Discussion

Sansudhi8's avatar
Sansudhi8
Icon for Helper III rankHelper III
6 years ago
Solved

Remove space between first name and last name

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...
Need Help
  • mussaenda's avatar
    mussaenda
    6 years ago

    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's avatar
Sansudhi8
Icon for Helper III rankHelper III
6 years ago

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 NameStatusCorrect name
GANEM-PEREZ,ANAValid 
JOUKHADAR, RENEInvalidJOUKHADAR,RENE
JOUKHADAR,RENEValid 
KATARIA, RAHULInvalidKATARIA,RAHUL
MAIDANA PAZ, CLAUDIAInvalidMAIDANA PAZ,CLAUDIA
NAVARRO,HEDSNA HOPEValid 
OSSAI,NDUKA-OBI FRANCISValid 
OSWALT, NICOLEInvalidOSWALT,NICOLE
OSWALT,NICOLEValid 
PEDDI, JAYAInvalidPEDDI,JAYA
PEREZ GOMEZ, CRISTINAInvalidPEREZ GOMEZ,CRISTINA
PRICE, KELLYInvalidPRICE,KELLY
RIVERA CRESPO,LUISValid 
RIVERA GUERRERO, JOSEInvalidRIVERA GUERRERO,JOSE
ROBINSON, MEGHANInvalidROBINSON,MEGHAN
ROBINSON,MEGHANValid 
ROCHA, HEDSNA HOPEInvalidROCHA,HEDSNA HOPE
SARETTE,WILLIAMValid 
SHANBHAG PAI, SANGEETHInvalidSHANBHAG PAI,SANGEETH
SHANBHAG PAI,SANGEETHValid 
WOODS, KRISTIInvalidWOODS,KRISTI
WOODS,KRISTIValid 

Sansudhi. 

mussaenda's avatar
mussaenda
Icon for Community Champion rankCommunity Champion
6 years ago

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"