Forum Discussion
Impactful IndividualReplacing values in a text column from related table
Most Valuable ProfessionalIn the exmaple below, I duplicate the original column to the column "Normalized" and perform the fixup of the new column by lowercasing the text, removing the ending dot and finding text that ends with "inc" to replace it with " inc".
You can copy the entire M statement to see the exmaple.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCq4sLknNVfDMS9ZTitWB8YFcJJ4CjOuRX1zgXpRfWoAQ8Q8OcA/yDw3w9HPGVAI0MhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Column1]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Original"}, {"Custom", "Normalized"}}),
#"Lowercased Text" = Table.TransformColumns(#"Renamed Columns",{{"Normalized", Text.Lower}}),
fnReplaceSuffix = (inputText, suffixToRemove, suffixToAdd) =>
if Text.EndsWith(inputText,suffixToRemove) then
Text.ReplaceRange(inputText,Text.Length(inputText)-Text.Length(suffixToRemove),Text.Length(suffixToRemove),suffixToAdd)
else inputText,
RemoveDots = Table.TransformColumns(#"Lowercased Text",{{"Normalized", each fnReplaceSuffix(_,".","")}}),
HandleInc = Table.TransformColumns(RemoveDots, {{"Normalized", each fnReplaceSuffix(_, "inc", " inc")}}),
#"Capitalized Each Word" = Table.TransformColumns(HandleInc,{{"Normalized", Text.Proper}})
in
#"Capitalized Each Word"
So, while the M statement may be complicated, you do get a much simplifier model.
Impactful IndividualHello Data--yes, if the normalizations are predictable. I should have been clearer that some systems have hundreds of facilities...and many of the transforms aren't cleanup of an entry but using the appropriate one.
CHS/Community Health System
Community Health System
CHS
All should become Community Health System. Hence it's a custom thing rather than a transform. Can you find/lookup and substitute a proper name during the query?
Thanks,
Tom
- Greg_Deckler
Community Champion
So, create a function and then create a new column that calls that function with the value that you want to lookup and have the function return the correction. Here is an example from an upcoming blog post on Hex to Decimal conversion. You should be able to use the same basic process. Note, your list of lookups and corrections could come from a data source such as a SQL table instead of the list that I created manually in "M" code:
let fnHex2Dec = (input) => let values = { {"0", 0}, {"1", 1}, {"2", 2}, {"3", 3}, {"4", 4}, {"5", 5}, {"6", 6}, {"7", 7}, {"8", 8}, {"9", 9}, {"A", 10}, {"B", 11}, {"C", 12}, {"D", 13}, {"E", 14}, {"F", 15} }, Result = Value.ReplaceType({List.First(List.Select(values, each _{0}=input)){1}},type {number}) in Result in fnHex2DecThis function takes a single input parameter, a single text character and translates it to a decimal equivalent. You can test this function by clicking the "Invoke" button in the Power Query Editor window. Be sure to enter a single value preceded by a single quote, such as 'A. The single quote forces the input to be recognized as text. This ensures that if you enter a 7, that it is recognized as text instead of a number.
One other note, you would have to add some logic for lookups that were correct, they would not be found so probably get a null result back or have to handle the error so you would have to wrap your call to this function in an "if" statement in the column code and if an error or null comes back, just use the original value. If I have some time, I'll play with it a little and see if I can get you even closer.
SeanThomasDay Can you create a table for the Uniform System Names?
This table will contain for each system all combinations of Provider ID and Hospital Name.
So your other table will lookup and get the correct Uniform System Name based on the Provider ID and Hospital Name?
I don't see how you can account for all mistakes people make unless you actually lookup the correct name?
What if it is supposed to be System CA Inc instead of System CO Inc but they are both Valid Names?
- ThomasDay
Greg_Deckler this is very promising. I have these in an excel file in a folder--or in a table in the model. The mistakes are quite consistent for each provider--generally done by the software that submits the data to cms and the "system name" seldom changes. I will have an excel file that someone that watches these things can update with a new "error".
Sean your land records watchout is a good one. This use-case is to simplify a menu/slicer of system names to select from so all the facilities from one system should be collapsed under one "commonly expected" system name. The "real" data we're very aware is what it is, so we're good in this particular situation. Thanks for thinking of that.
Also, Sean, perhaps 1/3->1/2 of facilities are in a system. And only 5% of those have abnormal system names. Does your idea work if you make a relationship between entered system name (Many to 1) and corrections just on the system name. If the relationship doesn't return an entry it means there isn't a correction---can I then just use the entered name like in smoupre's suggestion?
Tom
- Sean
you can of course get the Name entered instead of "Unknown" if there's no value in the Lookup table...
If the mistakes are consistent though as you say then you may not need this...
