Forum Discussion
Impactful IndividualReplacing values in a text column from related table
Community ChampionSo, 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.
Impactful IndividualGreg_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
Seanyou 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...
- ThomasDay
Sean--I'll try your solution in a different configuration in the am. First more context:
~6000 providers
~2500 in 500 systems
~700 of the 2500 have inconsistent names entered.
There are ~150 distinct inconsistent names resolving to 50 properly configured consistent names
Each year providers change systems (acquired) or systems themselves are acquired---it's not helpful to tie providers to a correct name, RATHER it's key to tie misspells (Hospital Grup) or inconsistencies (CHS should be Community Health Systems) to the proper names. That list is readily maintained without examining every provider every year--a slicer will have some stray names to be fixed.
So---I'll try your solution looking to relate the inconsistent name in the provider table--to the inconsistent name in the corrections table--and substitute the related consistent value. That is a many to ONE relationship like you're suggesting. That's what I was trying to do unsuccessfully. I will need to cover for no record found.
Greg_Deckler looks like you're building an M version of a switch function via a table "injection" of the switch values. That's exactly what I want to do. I'd love to know how to have the table feed your function values--perhaps you can give me a little nudge on how to feed the function. Actually I've not heard of building a function except through create column or table, so this is very intriguing and very useful.
Anyway, that's why this language is so confounding...handling text is still very foreign to me. Let me know what you all think and Thank you...
Tom
- Greg_Deckler
ThomasDay - Hey Thomas, I just created this in another thread:
Here is an example of a lookup function that uses a database table as a source of its information:
let fnHex2DecFromDB = (input) => let Source = Sql.Database("MySQLServer", "MyDatabase"), dbo_Hex2Dec = Source{[Schema="dbo",Item="Hex2Dec"]}[Data], Record = Table.First(Table.SelectRows(dbo_Hex2Dec, each [Hex] = input)), Result = Record.Field(Record,"Dec") in Result in fnHex2DecFromDBIn the example above, "MySQLServer" is the name of the SQL Server, "MyDatabase" is the name of the database. In that database on that server I have a table called "Hex2Dec" with two columns named "Hex" and "Dec". The rows are what you would imagine them to be for hex to decimial conversion. Basically, the premise here should work for any lookup situation that has unique lookup values. To use this, just create a new column with the formula:
=fnHex2DecFromDB([TextColumn])