Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query If and then statement

I have a database and need some help with Mean time between failure. I would like to take the DAX I have and convert it over to use in Power Query instead of DAX.

Mean Time Between Failure (MTBF) and Power BI - Microsoft Power BI Community

This is where I got my inspriration for the formula that I use currently. I works great but I need to have this done in Power Query so I can manipulate the data in Power Bi for Ranking and divding data up into Top and Bottom tens per each site I have. Below is the DAX statement I use. 

Barcode_MTBF_Hour =
VAR next = MINX(FILTER(tbl_Jobs_Super_Table,
tbl_Jobs_Super_Table[BarCode]=EARLIER(tbl_Jobs_Super_Table[BarCode]) &&
tbl_Jobs_Super_Table[Date_Ent]>EARLIER(tbl_Jobs_Super_Table[Date_Ent])
),tbl_Jobs_Super_Table[Date_Ent])
Return IF(ISBLANK((next)),
DATEDIFF(tbl_Jobs_Super_Table[Date_Warranty],NOW(),HOUR),
DATEDIFF(tbl_Jobs_Super_Table[Date_Warranty], next, HOUR))
I thought I could write something in a custom column that would do the same thing as this statement does.
I can load up some data for testing but am not sure how to do that.  I appreciate the help with this problem.
BarCodeDate_EntDate_WarrantyMTBF
A-14785311/12/20213/30/2022 
A-2360569/21/20211/6/2022 
A-2365455/1/20209/14/2020 
A-2365459/15/20206/2/2021 
A-2365611/31/20194/24/2019 
A-23656110/17/201912/20/2019 
A-2365611/27/20203/16/2020 
A-2365856/16/20201/14/2021 
A-2365851/15/20212/23/2021 
A-2365852/24/2021  
A-23660112/28/20188/19/2019 
A-23661312/19/201811/3/2020 
A-23661311/6/202012/1/2020 
A-23661312/2/2020  
A-2366397/3/20184/5/2019 
A-2366395/24/20197/24/2019 
A-2366397/24/201910/9/2020 
A-23663910/20/2020  
A-2366442/14/20204/24/2020 
A-2366448/11/202010/19/2020 
A-2366611/21/20209/5/2020 
A-2366619/9/202011/19/2020 
A-2366622/7/2011  
A-2366688/24/20184/12/2019 
A-2366684/12/20195/18/2020 
A-2366685/21/2020  
A-2366896/20/20199/12/2019 
A-2366899/12/20192/17/2020 
A-2366898/19/202011/17/2020 
A-23668911/17/20202/23/2021 
A-2367428/8/20197/22/2020 
A-60884812/5/20182/15/2020 
A-6088494/24/20199/29/2020 
A-6088499/29/20205/4/2021 
A-6088603/25/20195/30/2019 
A-6088606/10/20199/4/2019 
A-60886211/20/20196/5/2020 
A-60886211/10/20203/3/2022 
A-6088764/29/20206/9/2020 
A-6088787/11/2019  
A-6088788/20/20199/3/2019 
A-60887812/10/201912/16/2019 
A-6088787/8/20207/31/2020 
A-6088788/5/2020  
A-6088788/13/202111/11/2021 
A-6088783/22/2022  
A-6088888/5/201911/25/2019 
A-6088881/8/2020  
A-6088986/3/20217/26/2021 
A-60889812/6/20213/21/2022 
A-6089007/8/20207/22/2020 
A-76770610/14/202011/18/2020 
A-76770612/8/20201/5/2021 
A-7677077/28/20208/27/2021 
A-7677087/28/20202/1/2021 
A-7677097/30/2020  
A-7677107/30/20208/25/2020 
Need Help
Show and Tell
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    First add a custom column to get the Next Date_Ent on every row. 

    List.Min(Table.Column(Table.SelectRows(#"previous step name",(x)=> x[BarCode]=[BarCode] and x[Date_Ent]>[Date_Ent]),"Date_Ent"))

    Then compare [Next] with [Date_Warranty] to get total duration hours.

    if [Next] = null then Duration.TotalHours(DateTime.LocalNow()-([Date_Warranty] & #time(0,0,0))) else Duration.TotalHours([Next]-[Date_Warranty])

    Last, change MTBF column to whole number type. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    First add a custom column to get the Next Date_Ent on every row. 

    List.Min(Table.Column(Table.SelectRows(#"previous step name",(x)=> x[BarCode]=[BarCode] and x[Date_Ent]>[Date_Ent]),"Date_Ent"))

    Then compare [Next] with [Date_Warranty] to get total duration hours.

    if [Next] = null then Duration.TotalHours(DateTime.LocalNow()-([Date_Warranty] & #time(0,0,0))) else Duration.TotalHours([Next]-[Date_Warranty])

    Last, change MTBF column to whole number type. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.