Search code examples
powerbipowerquerypowerpivot

Logic to find FIRST PASS RATE (Number of batch passed test the first time they came for testing?) for quality data in power query editor

enter image description here

I'm working on a quality set of data and trying to find the first pass rate. Attached picture shows a sample of lab testing data. Batch_id is a unique Id for every batch being produced and their respective result is in the Pass/fail column. If a batch fails the first time it comes to test then it has to be fixed and brought again for testing (thus you'll see same batch_id repeating) - this will go on untill that respective batch is passed. I'm trying to create a logic in power query to find the First pass rate of the batches - meaning, (total number of batches that passed the first time they came in)/(total batches). Can anyone guide me write a logic on the above problem, I'm new to power query and still learning my way through.

Date          Time        Batch_id      Pass/Fail
4/20/2023   10:00 AM        100         Yes
4/20/2023   11:00 AM        101         No
4/20/2023   11:15 AM        101         Yes
4/20/2023   11:35 AM        102         No
4/20/2023   11:55 AM        102         No
4/20/2023   12:30 PM        102         Yes
4/20/2023   12:50 PM        103         Yes
4/20/2023   1:30  PM        104         Yes
4/20/2023   1:45 PM         105         No
4/20/2023   1:55 PM         105         No
4/20/2023   2:15 PM         105         Yes
4/20/2023   2:35 PM         106         Yes
4/20/2023   2:55 PM         107         No
4/20/2023   3:15 PM         107         Yes
4/20/2023   3:35 PM         108         Yes
4/20/2023   3:45 PM         109         No
4/20/2023   4:30 PM         109         Yes
4/20/2023   4:50 PM         110         Yes
4/20/2023   5:30 PM         111         No
4/20/2023   5:55 PM         111         No
4/20/2023   6:30 PM         112         Yes
Solution

  • In Power Query, reshape the data so the Pass/Fail columns returns true or false depending on whether or not the earliest test is classified as Yes

    let

//next lines just read in your data from a pasted table
//and split it up into the separate columns
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldNRC4IwEAfwryI+B263Tcu3InorfOglRGKUoFD0oN+fNkLptnmr4cvhz+PvcavrdK/HNpnPuX/O1U6Pt+7a3z9VpYchO+j+kTarOpUZMPOAMG84KxlLtsfpO26q6Vzawffc9Xz2p1eIc7XAF9oL7CHSXv3DoRQsqYI8mAZKhb2gvW2PvIx4qb648YqMb3/2dw529As8lAbs6JHPI96NU1BxhBenINsLL8464t1hbqg4Eq8C4qH2Eq+CWT36oii3PycvinKHSfPc6+5scvMG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by WhiteSpace" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByWhitespace(QuoteStyle.None),
        {"Date","Time","AM/PM","Batch_id","Pass/Fail"}),
    #"Removed Top Rows" = Table.Skip(#"Split Column by WhiteSpace",1),

//Create a Date/Time column from your data and remove the original
    #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "Date/Time", each DateTime.FromText(Text.Combine({[Date],[Time],[#"AM/PM"]}," "))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date", "Time", "AM/PM"}),

//Group by Batch_id
//Then return "true" if the earliest item in the table is "true"
//   else return "false"
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Batch_id"}, {
        {"Passed on First Test", (t)=>
            Table.SelectRows(t, 
                each [#"Date/Time"] = List.Min(t[#"Date/Time"])){0}[#"Pass/Fail"]="Yes", type logical}       
       })
in
    #"Grouped Rows"

    enter image description here

    There are probably many ways to display the results. Here's one

    • Close and Apply
    • Select the Card visual
    • Add the following measures:
         Total Rows = COUNTROWS('Table')
     % of Total = DIVIDE([Total Rows],CALCULATE([Total Rows],ALL('Table'[Passed on First Test])))

    Add the measure to the Card visual and format as percent, filtering or slicing for true

    enter image description here

    Here's a screen shot with both the Card visual and a slicer, so you select either:

    enter image description here