Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thank you.
Sansudhi.
Solved! Go to 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"
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"
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.
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.
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
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.
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 , 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |