Forum Discussion
Unifying Percentage Data Formats in a Single Column
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.
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
4 Replies
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
Thank you HotChilli! That syntax solved my problem.
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!
AThank 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.
