Forum Discussion

DBirdmanAR's avatar
DBirdmanAR
Regular Visitor
9 years ago
Solved

How do I display a data grid in PowerBI

Hi All,   I want to display a simple data grid in PowerBI and format the cells red for TestResults=Pos and green for TestResults=Neg.  Ultimately the data would look something like this (from MS Ex...
  • DBirdmanAR's avatar
    DBirdmanAR
    9 years ago

    Something like this: 

     

    Notice that Patient C does not data for certain days and there is one Unk (Day 2 of Patient A).

     

     

    = Table.FromRows({
        {
            "A","A1",
            1,
            1,
            "Pos",
            "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "A","A2",
            1,
            2,
            "Unk",
            ""
        },
        {
            "A","A3", 1,
            4,
            "Neg"  ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        },
        {
            "A","A4",
            1,
            6,
            "Pos",
            "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "A","A5",
            1,
            12,
            "Pos",
            "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "A","A6",
            1,
            24,
            "Pos","https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "A","A7",
            1,
            48,
            "Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        },
        {
            "B","B1",
            2,
            1,
            "Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        },
        {
            "B","B2",
            2,
            2,
            "Pos" ,"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "B","B3",
            2,
            4,
            "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        },
        {
            "B","B4",
            2,
            6,
            "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        },
        {
            "B","B5",
            2,
            12,
            "Pos","https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "B","B6",
            2,
            24,
            "Pos",
            "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "B","B7",
            2,
            48,
            "Pos",
            "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "B","B8",
            2,
            64,
            "Pos",
            "https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "C","C1",
            3,
            1,
            "Neg" ,"https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        },
        {
            "C","C2",
            3,
            2,
            "Pos" ,"https://cdn4.iconfinder.com/data/icons/keynote-and-powerpoint-icons/256/Plus-128.png"
        },
        {
            "C","C3",
            3,
            4,
            "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        },
        {
            "C","C4",
            3,
            6,
            "Neg","https://cdn3.iconfinder.com/data/icons/softwaredemo/PNG/128x128/Minus_Circle_Green.png"
        }
    }, {
        "PatientID","PatientIDNumber",
        "YHelper",
        "StudyDay",
        "TestResults",
        "Image"
    })
  • Anonymous's avatar
    Anonymous
    9 years ago

    Though it has Known Limitations (see https://powerbi.microsoft.com/en-us/documentation/powerbi-service-r-visuals/#known-limitations), an R visual can do it.  See below for sample R script (12 lines without comments and blanks) and output.

     

    library(ggplot2)    # Needed for ggplot
    library(ggthemes)   # has a clean theme for ggplot2
    library(tidyr)      # provide 'complete' function for empty heatmap cells
    
    #Convert the (integer) StudyDay to a factor to avoid gaps in the X axis
    dataset$StudyDay=with(dataset,factor(StudyDay))
    
    #Fill missing data values, to avoid blank cells in the heatmap
    dataset <- dataset %>% complete(PatientID, StudyDay)
    
    # Avoid having to reference dataset$ each time in function calls
    attach(dataset)
    
    #Plot the dataset with TestResults to set the fill colour
    ggplot(dataset, aes(x=StudyDay, y=PatientID, fill=TestResults)) +
    
    #geom_tile to present as grid per https://www.rstudio.com/wp-content/uploads/2015/12/ggplot2-cheatsheet-2.0.pdf
      geom_tile(color="black", size=0.1) +
    
    #Set the fill colours for TestResults
      scale_fill_manual(values = c("Pos" = "green", "Neg" = "red", "Unk" = "blue")) +
    
    #Set square tiles
      coord_equal() +
    
    #Apply tidy Tufte theme with no axis ticks
      theme_tufte(base_family="arial", ticks=FALSE) +
    
    #Order by reverse PatientID
      scale_y_discrete(limits = rev(levels(PatientID)))