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.
I have a dataset that has a primary code for each transaction and then in a separate column has all related codes for that transaction. Probably easiest to just describe it in an example. So for instance the primary code for the first record is F0390 ("ICD-10" column). Then the "All ICD-10" column contains all relevant codes for that transaction, including the primary code. So in the first record "F0390" and "R5381". What I am hoping to do is remove the primary code from the column with all relevant codes. So in the first record, I want to remove "F0390" from the "All ICD-10" column. In the second record, I want to remove "R5381" from the "All ICD-10" column. Is there a way to do this in Power Query?
Solved! Go to Solution.
Hi,
maybe this can help. Refer to your table in Source step.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
CLEAN = Table.ReplaceValue(Table.TransformColumns(Table.AddColumn(Source, "All ICD-10 Replaced",
each if Text.Contains([#"All ICD-10"], [#"ICD-10"])
then Text.Replace([#"All ICD-10"], [#"ICD-10"], "")
else [#"All ICD-10"]),
{{"All ICD-10 Replaced", Text.Trim, type text}}),
" "," ",Replacer.ReplaceText,{"All ICD-10 Replaced"})
in
CLEAN
Hi,
maybe this can help. Refer to your table in Source step.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
CLEAN = Table.ReplaceValue(Table.TransformColumns(Table.AddColumn(Source, "All ICD-10 Replaced",
each if Text.Contains([#"All ICD-10"], [#"ICD-10"])
then Text.Replace([#"All ICD-10"], [#"ICD-10"], "")
else [#"All ICD-10"]),
{{"All ICD-10 Replaced", Text.Trim, type text}}),
" "," ",Replacer.ReplaceText,{"All ICD-10 Replaced"})
in
CLEAN
This worked for a project for me. Is there a way to make it match if it begins with the data in the ICD-10 column and not the entire column? For instance the ICD-10 column, the data might be F0390.0 instead of just F0390 but you still want to remove it from the All ICD-10 column
Awesome, that worked! Thanks a lot for the help!
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.