Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  Change data based on multiple conditions

    Posted May 12, 2022 06:26 PM
    Hello,
    I have a data set (6 columns) and multiple rows, and I want to make some data transformations.

    Lot No Title Lot Status Title Lot Created On Lot Opened On Lot Closed On
    210216 - SIL TRIAL- SMOOTH DRUM Name1 Cancelled 16/02/2021    
    WSA10-AARU-DRG-RCBC-000001 Name2 Open Lot 16/03/2022 16/03/2022  
    WSA10-AARU-DRG-RCBC-000002 Name3 Open Lot 4/04/2022 31/03/2022  
    WSA10-AARU-DRG-RCBC-000003 Name4 Assign Lot Number 14/04/2022    
    WSA10-AARU-DRG-RCBC-000004 Name5 Open Lot 2/05/2022 2/05/2022  
    WSA10-AARU-ENV-CLNR-000001 Name6 Open Lot 3/03/2022 3/03/2022  
    WSA10-AARU-EWK-CLGR-000001 Name7 Open Lot 3/03/2022 3/03/2022  
    WSA10-AARU-EWK-CLGR-000002 Name8 Open Lot 3/03/2022 3/03/2022  
    WSA10-AARU-EWK-CLGR-000003 Name9 Open Lot 3/03/2022 3/03/2022 20/04/2021
    WSA10-AARU-EWK-EXCV-000001 Name10 Open Lot 9/05/2022 9/05/2022 20/04/2021
    WSA10-AARU-EWK-TOPS-000001 Name11 Open Lot 4/05/2022 4/05/2022 20/04/2021
    WSA10-AARU-EWK-TOPS-000002 Name12 Assign Lot Number 4/05/2022    
    WSA10-AARU-UTS-ELER-000001 Name13 Open Lot 28/03/2022 28/03/2022  
    WSA10-AARU-UTS-TELE-000001 Name14 Open Lot 6/05/2022 6/05/2022  
    WSA10-AARU-UTS-WATE-000001 Name15 Open Lot 11/05/2022 10/05/2022  
    WSA10-ASAR-DRG-BACK-000001 Name16 Cancelled 10/12/2019    
    WSA10-ASAR-EWK-GNFL-000001 Name17 Open Lot 8/11/2021 8/11/2021  
    WSA10-ASAR-EWK-REPT-000001 Name18 Cancelled 12/02/2021    
    WSA10-ASAR-EWK-REPT-000002 Name19 Closed-out 24/02/2021 1/03/2021 20/04/2021
    WSA10-ASAR-PCC-RCPM-000001 Name20 Open Lot 10/11/2021 10/11/2021  
    WSA10-ASAR-STR-REIN-000001 Name21 Open Lot 11/11/2021 11/11/2021  
    WSA10-BASIN 2-EWK-CLGR-000001 Name22 Closed-out   13/10/2021 8/11/2021
    WSA10-BASIN10-DRG-DRML-000001 Name23 Open Lot 26/10/2021 26/10/2021  
    WSA10-BASIN10-DRG-DRML-000002 Name24 Open Lot 1/12/2021 26/10/2021  
    WSA10-BASIN10-DRG-DRML-000003 Name25 Closed-out 8/12/2021 15/12/2021 22/04/2022
    WSA10-BASIN10-DRG-DRML-000004 Name26 Closed-out   13/12/2021 11/01/2022
    WSA10-BASIN10-DRG-DRML-000005 Name27 Lot Completion Notification 4/04/2022 12/04/2022  


    1- To delete the date in column F (Lot close on) if the Status in column C (Lot Status Title) is Open lot (see text in bold)
    2- To add the same date in column D (Lot created on) in column E (Lot Opened On) if Status is Open lot and Open date is blank
    In general, I want to check column C for various statuses and change the date accordingly based on the specific condition.
    Thanks
    Ahmed

    ------------------------------
    Ahmed Kamal
    Quality Engineer
    ------------------------------


  • 2.  RE: Change data based on multiple conditions

    Bronze Contributor
    Posted May 13, 2022 02:57 AM
    Have a look at Step 3 in Replace Values in Power Query M (Ultimate Guide) - Gorilla BI .

    Basically, you want to add an M language step after the data load, with something like

    = Table.ReplaceValue(#"Source",each [Lot Closed On], each if [Lot Status Title] = "Open Lot" then null else [Lot Closed On],Replacer.ReplaceValue,{"Lot Closed On"})

    (i.e. don't do it in DAX, do data transform steps after loading the data)



    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------

    Original Message


  • 3.  RE: Change data based on multiple conditions

    Posted May 15, 2022 08:37 PM
    What is "Source" referred to in the step?

    ------------------------------
    Ahmed Kamal
    Quality
    ------------------------------

    Original Message


  • 4.  RE: Change data based on multiple conditions

    Bronze Contributor
    Posted May 16, 2022 02:52 AM
    #"Source" is the reference to a previous step that loads the data, e.g. if you were loading data from Oracle:

    let
    Source = Oracle.Database("max75p", [Query="Select..  from ....", CreateNavigationProperties=false]),

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------

    Original Message


  • 5.  RE: Change data based on multiple conditions

    Posted May 16, 2022 09:06 PM
    Thank you, I have added the following queries:

    = Table.ReplaceValue(#"Changed Type3",each[Lot Closed On],each if [Status] <> "Closed-out" then null else [Lot Closed On],Replacer.ReplaceValue,{"Lot Closed On"})
    = Table.ReplaceValue(#"Replaced Value",each [Lot Created On],each if [Lot Created On] = null then [Lot Opened On] else [Lot Created On],Replacer.ReplaceValue,{"Lot Created On"})
    = Table.ReplaceValue(#"Replaced Value1",each[Lot Opened On],each if [Status] = "Cancelled" then null else [Lot Opened On],Replacer.ReplaceValue,{"Lot Opened On"})
    = Table.ReplaceValue(#"Replaced Value2",each[Lot Opened On],each if [Status] = "Assign Lot Number" then null else [Lot Opened On],Replacer.ReplaceValue,{"Lot Opened On"})

    and now I want to add one more replace the value in [Lot opened on] to check for "lot completion notification" in [Status] and null values in lot opened on and if this condition is correct add [ Lot created on]

    Can you help please?


    ------------------------------
    Ahmed Kamal
    Quality
    ------------------------------

    Original Message


  • 6.  RE: Change data based on multiple conditions

    Bronze Contributor
    Posted May 17, 2022 03:37 AM

    = Table.ReplaceValue(#"Replaced Value3",each[Lot Opened On],

    each if [Status] = "Lot Completion Notificatin" and [Lot Opened On] then  [Lot Created On] else [Lot Opened On],

    Replacer.ReplaceValue,

    {"Lot Opened On"})



    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------

    Original Message


  • 7.  RE: Change data based on multiple conditions

    Posted May 17, 2022 03:50 AM
    Thanks but it is not working

    = Table.ReplaceValue(#"Replaced Value3",each[Lot Opened On],

    each if [Status] = "Lot Completion Notificatin" and [Lot Opened On] then  [Lot Created On] else [Lot Opened On],

    Replacer.ReplaceValue,

    {"Lot Opened On"})

    The out put should be replace any null value in [Lot Opened on] with the value in [Lot Created on] if [Status] = Lot Completion Notification
    Could you please check again?



    ------------------------------
    Ahmed Kamal
    Quality
    ------------------------------

    Original Message


  • 8.  RE: Change data based on multiple conditions

    Posted May 17, 2022 04:01 AM
    I solved it:

    = Table.ReplaceValue(#"Replaced Value3",each[Lot Opened On], each if [Status] = "Lot Completion Notification" and [Lot Opened On] = null then [Lot Created On] else [Lot Opened On], Replacer.ReplaceValue, {"Lot Opened On"})

    I guess you just missed null in the and argument. Thanks,

    ------------------------------
    Ahmed Kamal
    Quality
    ------------------------------

    Original Message


  • 9.  RE: Change data based on multiple conditions

    Posted May 23, 2022 11:58 PM
    Edited by Ahmed Kamal May 24, 2022 05:46 PM
    I have the data set as per the below file:
    The below graph has been developed in Excel... How can I get the same result in Power BI?
    My challenge is that I want to create a new table in Power BI with the following information:
    1- Count the open lots per each month from the [Lot Opened on] column in the above data set and make it count in the below new table
    2- Count the closed lots per each month from the [Lot Closed on] column in the above data set and make it count in the below new table
    3- Calculate the cumulative open lot and closed lot each month
    4- calculate the percentage of closed lots (Closed/open) and blot the closure percentage every month as below.


    ------------------------------
    Ahmed Kamal
    Quality
    ------------------------------

    Original Message