Unifying Percentage Data Formats in a Single Column

[rmussetter]

Hello,

I am trying to create a table-widget(?) that will indicate the number of patients at our health center that have a lab result that falls within certain categories (i.e. <7%, 7 to 8%, >8%). When I pull the raw data from our electronic health record (EHR), the data is entered either as a true number (i.e. "0.07") or as a percentage (i.e. "7"). Is there a way to change the formatting for only a portion of a column? Thanks. I am building my first dashboard and, aside from reading about 400 pages of a user guide, know nothing.

[Anonymous]

Hey @rmussetter 
In addition to the above from HotChilli ,
It is also a good opertunity for some data cleansing and alignments in you EHR. In addition, need to train the users to insert the correct numbers. Last, but not least, customise the system to accept only one type of numbers (0-1/0-100),

Raise this as an issue with your data team and/or DBA , or whom ever is in charge of that system (system admin, etc.)

Good luck!
A

[rmussetter]

Thank you @Anonymous . This is a good point and I will raise it with our organization's data governance group. In the meantime, HotChilli's syntax solution works to bring me to the place I need to be.

[HotChilli · Accepted solution]

It would probably make sense to clean the data up in Power Query editor.

You could add a custom column , something like

if [labvalue] > 1 then [labvalue] else 100 * [labvalue]

as long as that rule holds for all of your data

[rmussetter]

Thank you @HotChilli! That syntax solved my problem.