Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
3 years ago
Solved

Conditional with same/diff Fields.

Hi all,

 

I have an Excel file with customer information, CUID (unique customer number) + PM Name + method

CUID ending with "i" means integration. CUIDs can be unique and I can also have the same CUID ending with "i".

 

I trying to figure out a formula saying if PM is the same working with the normal CUID + Same CUID (ending with "i") then Fast Track, otherwise 2 Cycle. See table below.

 

Customer CUID Project Manager / Coordinator Method
Client A 1002187BE01 John Smith Fast Track
Client A 1002187BE01i John Smith Fast Track
Client A 1002187ES01 Lebron James 2 Cycle
Client A 1002187ES01i Luka Doncic 2 Cycle
Client A 1002187FR01 Silvia Vazquez Fast Track
Client A 1002187FR01i Silvia Vazquez Fast Track

 

any tip? thanks

 

jgeddes 

 

 

  • You can do this in Power Query.
    I commented out the steps to explain what is going on.

    Hope this helps

    let
    //copied in source data from table on web
        Source = 
        Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUXBU0lEyNDAwMrQwd3I1MATyvPIz8hSCczNLMoAct8TiEoWQosTkbKVYHVyaMknW5RoMtsonNakoP0/BKzE3tRjINVJwrkzOScWjB2STT2l2ooJLfl5yZjJBPW5BYHuCM3PKMhMVwhKrCktTq4hxIEhjJiGdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, CUID = _t, #"Project Manager" = _t, Method = _t]),
        changeDataTypes = 
    //set data types to text
        Table.TransformColumnTypes(
            Source,
            {{"Customer", type text}, {"CUID", type text}, {"Project Manager", type text}, {"Method", type text}}
        ),
    //add a column that tests for 'i' at the end of CUID
        addIntegrationTest = 
        Table.AddColumn(
            changeDataTypes, 
            "Integration", 
            each if Text.EndsWith([CUID], "i") then "Yes" else "No"
        ),
    //add a column that combines 'Customer', 'CUID' and 'Project Manager'
    //removes the 'i' from the CUID if it is present
        addAmendCUID = 
        Table.AddColumn(
            addIntegrationTest, 
            "amendCUID", 
            each if Text.EndsWith([CUID], "i") then [Customer]&Text.BeforeDelimiter([CUID], "i")&[Project Manager] else [Customer]&[CUID]&[Project Manager]
        ),
    //add a column that contains a list of 'amendCUID' values that have 'Integration' equal to 'Yes'
        addYesList = 
        Table.AddColumn(
            addAmendCUID, 
            "yesList", 
            each Table.Column(                      //turns the 'amendCUID' column into a list
                Table.SelectRows(                   //selects rows from the table that have 'Integration' equal to 'Yes'
                    addAmendCUID, 
                    each [Integration] = "Yes"
                ), 
                "amendCUID")
        ),
    //add a column that counts the instances of the 'amendCUID' value in the list of all 'amendCUID' values
        addAmendCUIDCount = 
        Table.AddColumn(
            addYesList, 
            "countCUID", 
            (x)=> List.Count(               //function that counts the number of times the current row 'amendCUID' value occurs
                List.Select(                //create a list that contains only values equal to the current row 'amendCUID' value
                    Table.Column(           //create a list of all 'amendCUID' values
                        addYesList, 
                        "amendCUID"
                    ), 
                each _ = x[amendCUID]
                )
            )
        ),
    //add a conditional column that displays 'Fast Track' if the current row 'amendCUID' value is in the 'yesList' and if 'countCUID' value is greater than 1
    //'2 Cycle' is displayed if those conditions are not met
        addMethod = 
        Table.AddColumn(
            addAmendCUIDCount, 
            "Method2", 
            each if List.Contains([yesList], [amendCUID]) = true and [countCUID] > 1 then "Fast Track" else "2 Cycle"
        ),
    //remove columns that are no longer needed
        removeColumns = 
        Table.RemoveColumns(
            addMethod,
            {"Integration", "amendCUID", "yesList", "countCUID"}
        )
    in
        removeColumns

5 Replies

  • You can do this in Power Query.
    I commented out the steps to explain what is going on.

    Hope this helps

    let
    //copied in source data from table on web
        Source = 
        Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUXBU0lEyNDAwMrQwd3I1MATyvPIz8hSCczNLMoAct8TiEoWQosTkbKVYHVyaMknW5RoMtsonNakoP0/BKzE3tRjINVJwrkzOScWjB2STT2l2ooJLfl5yZjJBPW5BYHuCM3PKMhMVwhKrCktTq4hxIEhjJiGdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, CUID = _t, #"Project Manager" = _t, Method = _t]),
        changeDataTypes = 
    //set data types to text
        Table.TransformColumnTypes(
            Source,
            {{"Customer", type text}, {"CUID", type text}, {"Project Manager", type text}, {"Method", type text}}
        ),
    //add a column that tests for 'i' at the end of CUID
        addIntegrationTest = 
        Table.AddColumn(
            changeDataTypes, 
            "Integration", 
            each if Text.EndsWith([CUID], "i") then "Yes" else "No"
        ),
    //add a column that combines 'Customer', 'CUID' and 'Project Manager'
    //removes the 'i' from the CUID if it is present
        addAmendCUID = 
        Table.AddColumn(
            addIntegrationTest, 
            "amendCUID", 
            each if Text.EndsWith([CUID], "i") then [Customer]&Text.BeforeDelimiter([CUID], "i")&[Project Manager] else [Customer]&[CUID]&[Project Manager]
        ),
    //add a column that contains a list of 'amendCUID' values that have 'Integration' equal to 'Yes'
        addYesList = 
        Table.AddColumn(
            addAmendCUID, 
            "yesList", 
            each Table.Column(                      //turns the 'amendCUID' column into a list
                Table.SelectRows(                   //selects rows from the table that have 'Integration' equal to 'Yes'
                    addAmendCUID, 
                    each [Integration] = "Yes"
                ), 
                "amendCUID")
        ),
    //add a column that counts the instances of the 'amendCUID' value in the list of all 'amendCUID' values
        addAmendCUIDCount = 
        Table.AddColumn(
            addYesList, 
            "countCUID", 
            (x)=> List.Count(               //function that counts the number of times the current row 'amendCUID' value occurs
                List.Select(                //create a list that contains only values equal to the current row 'amendCUID' value
                    Table.Column(           //create a list of all 'amendCUID' values
                        addYesList, 
                        "amendCUID"
                    ), 
                each _ = x[amendCUID]
                )
            )
        ),
    //add a conditional column that displays 'Fast Track' if the current row 'amendCUID' value is in the 'yesList' and if 'countCUID' value is greater than 1
    //'2 Cycle' is displayed if those conditions are not met
        addMethod = 
        Table.AddColumn(
            addAmendCUIDCount, 
            "Method2", 
            each if List.Contains([yesList], [amendCUID]) = true and [countCUID] > 1 then "Fast Track" else "2 Cycle"
        ),
    //remove columns that are no longer needed
        removeColumns = 
        Table.RemoveColumns(
            addMethod,
            {"Integration", "amendCUID", "yesList", "countCUID"}
        )
    in
        removeColumns
    • romovaro's avatar
      romovaro
      Responsive Resident

      HI Jgeddes

       

      Thanks. I will try to do it following your steps.

      Just for clarification, the "method" column doesn't exist in my table. It was just to show the result I want to see. Customer, CUID and Project manager/Coordinator are included.

       

      For the part below:

       

       

      let
      //copied in source data from table on web
          Source = 
          Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUXBU0lEyNDAwMrQwd3I1MATyvPIz8hSCczNLMoAct8TiEoWQosTkbKVYHVyaMknW5RoMtsonNakoP0/BKzE3tRjINVJwrkzOScWjB2STT2l2ooJLfl5yZjJBPW5BYHuCM3PKMhMVwhKrCktTq4hxIEhjJiGdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, CUID = _t, #"Project Manager" = _t, Method = _t]),

      I don't truly understand the step. DO I need to create a Custom Column and use my File path ?

      • jgeddes's avatar
        jgeddes
        Super User

        I left your method column in my example just to highlight that the same result was achieved. I did not use it in any way.
        The 'Source' step in the query code is just a copy of the data you posted to the web. You should not have to make any changes to your existing query source code. You would be looking to use all of my code but excluding my source. (Use your source.) You will likely need to change column names in my code to mathc the column names in your code.

  • romovaro's avatar
    romovaro
    Responsive Resident

    Hi

     

    I added my Data Source path and updated column name for Product manager to 'Project Manager / Coordinator but it seems is not working.

     

    = Table.AddColumn(#"Changed Type", "Method", each let
    //copied in source data from table on web
    Source =
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("https://adponline.sharepoint.com/sites/implementation-central-services/celergo-implementation/Shared%20Documents/00%20-%20Reports/01_Guidance%20documents/Source%20files/PV%20Celergo%20Report.xlsx=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, CUID = _t, #"Project Manager / Coordinator" = _t, Method = _t]),
    changeDataTypes =
    //set data types to text
    Table.TransformColumnTypes(
    Source,
    {{"Customer", type text}, {"CUID", type text}, {"Project Manager / Coordinator", type text}, {"Method", type text}}
    ),
    //add a column that tests for 'i' at the end of CUID
    addIntegrationTest =
    Table.AddColumn(
    changeDataTypes,
    "Integration",
    each if Text.EndsWith([CUID], "i") then "Yes" else "No"
    ),
    //add a column that combines 'Customer', 'CUID' and Project Manager / Coordinator
    //removes the 'i' from the CUID if it is present
    addAmendCUID =
    Table.AddColumn(
    addIntegrationTest,
    "amendCUID",
    each if Text.EndsWith([CUID], "i") then [Customer]&Text.BeforeDelimiter([CUID], "i")& [Project Manager / Coordinator] else [Customer]&[CUID]&[ Project Manager / Coordinator]
    ),
    //add a column that contains a list of 'amendCUID' values that have 'Integration' equal to 'Yes'
    addYesList =
    Table.AddColumn(
    addAmendCUID,
    "yesList",
    each Table.Column( //turns the 'amendCUID' column into a list
    Table.SelectRows( //selects rows from the table that have 'Integration' equal to 'Yes'
    addAmendCUID,
    each [Integration] = "Yes"
    ),
    "amendCUID")
    ),
    //add a column that counts the instances of the 'amendCUID' value in the list of all 'amendCUID' values
    addAmendCUIDCount =
    Table.AddColumn(
    addYesList,
    "countCUID",
    (x)=> List.Count( //function that counts the number of times the current row 'amendCUID' value occurs
    List.Select( //create a list that contains only values equal to the current row 'amendCUID' value
    Table.Column( //create a list of all 'amendCUID' values
    addYesList,
    "amendCUID"
    ),
    each _ = x[amendCUID]
    )
    )
    ),
    //add a conditional column that displays 'Fast Track' if the current row 'amendCUID' value is in the 'yesList' and if 'countCUID' value is greater than 1
    //'2 Cycle' is displayed if those conditions are not met
    addMethod =
    Table.AddColumn(
    addAmendCUIDCount,
    "Method2",
    each if List.Contains([yesList], [amendCUID]) = true and [countCUID] > 1 then "Fast Track" else "2 Cycle"
    ),
    //remove columns that are no longer needed
    removeColumns =
    Table.RemoveColumns(
    addMethod,
    {"Integration", "amendCUID", "yesList", "countCUID"}
    )
    in
    removeColumns)

     

    What I did:

     

     

     

     

    it also shows an error when replacing name column