R Script Power Query: New character column read as numbers in Power BI

Question

I'm using Power BI to create a report, but I'm doing the data cleaning in an R Script in Power Query Editor because I couldn't conceive of how to do it in DAX. For some reason, a new character column that I've created is being read as numbers, similar to what would happen if you read factor levels as integers. Here's some example data.

+-----------+-------------------------+-----------------------------------+
| PatientId |       LabTestName       |           LabTestResult           |
+-----------+-------------------------+-----------------------------------+
|         1 | Veritor Rapid Influenza | NEGATIVE TYPE A / NEGATIVE TYPE B |
|         1 | Veritor Rapid Strep     | Cancelled                         |
|         1 | NA                      | NA                                |
|         2 | NA                      | NA                                |
|         3 | Veritor Rapid Strep     | Negative                          |
|         3 | SARS CoV 2 RNA, RT PCR  | NOT DETECTED                      |
+-----------+-------------------------+-----------------------------------+

Some example code:

x %>%
  group_by(PatientId) %>%
  mutate(
    CoV_Tested = any(!is.na(LabTestName) & str_detect(LabTestName, "CoV")),
    CoV_Result = ifelse(
      CoV_Tested, unique(LabTestResult[!is.na(LabTestName) & str_detect(LabTestName, "CoV")]), NA
    )
  ) %>%
  distinct(PatientId, CoV_Tested, CoV_Result)

In the resulting table below, Power BI reads CoV_Tested as logical, as expected, but CoV_Result is read as text.

+-----------+------------+--------------+
| PatientId | CoV_Tested |  CoV_Result  |
+-----------+------------+--------------+
|         1 |   FALSE    | <NA>         |
|         2 |   FALSE    | <NA>         |
|         3 |    TRUE    | NOT DETECTED |
+-----------+------------+--------------+

The value "NOT DETECTED" is read as 4. I've reproduced this with my large data set and this smaller example one. I also can't just tell Power BI to change the data type to "text" because it just changes the 4 to "4" (ie, changes the numeric "4" to text "4"). I might just be able transform the column in Power BI to replace the numbers with the actual text, but this is a step that shouldn't be required, and would be a hassle if there were a lot of unique values.

Any help would be appreciated! Thanks!

Alexis Olson Alexis Olson · Accepted Answer · 2020-04-06T20:59:35

I'm not sure why it interacts with R like this but it's not too hard to do this just in M with the help of a custom column.

#"Added Custom" = Table.AddColumn(#"Previous Step",
    "CoV", each if [LabTestName] <> null and Text.Contains([LabTestName], "CoV")
                then [LabTestResult] else null, type text),
#"Grouped Rows" = Table.Group(#"Added Custom",
    {"PatientId"},
    {{"CoV_Tested", each List.MatchesAny([CoV], each _ <> null), type logical},
     {"CoV_Result", each List.First(List.Select([CoV], each _ <> null)), type text}})

R Script Power Query: New character column read as numbers in Power BI

1 Answers