Conditional with same/diff Fields.

[romovaro]

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 

[romovaro]

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...=", 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

[jgeddes · Accepted solution]

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]

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]

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]

HI @jgeddes 

Still working on your solution.

On the other hand, I tried to find other solutions.

I created a custom formula to remove the "i" from the integration CUID.

if Text.End([CUID],1)="i" then Text.Start([CUID],Text.Length([CUID])-1) else [CUID]

And created another column to find duplicates. If I have a duplicate, then I have the integration + normal project. 

Payroll+Integration =

VAR varCurrentValue = 'PV Cel Report'[CUID_i]

VAR varInstances =

    COUNTROWS(

        FILTER(

            'PV Cel Report',

            'PV Cel Report'[CUID_i] = varCurrentValue

        )

    )

var Result =

    IF(

        varInstances > 1,

        "Yes",

        "No"

    )

RETURN

    Result

 Then I have the following table with the added "CUID_i" column + Payroll Integration COlumn

	Customer	CUID	Project Manager / Coordinator	CUID_i	Payroll+Integration
1	Client A	1002187BE01	John Smith	1002187BE01	Yes
2	Client A	1002187BE01i	John Smith	1002187BE01	Yes
3	Client A	1002187ES01	Lebron James	1002187ES01	Yes
4	Client A	1002187ES01i	Luka Doncic	1002187ES01	Yes
5	Client B	1002187CA01	John Smith	1002187BE01	Yes
6	Client B	1002187CA01i	John Smith	1002187BE01	Yes

THe idea now is to get a formula.

If Payroll + Integration = yes

&&

Project manager/Coordinator from is the same in both lines then "yes", "no")

In the table above, Client A (+row 1 and 2) = yes

In the table above, Client A (+row 3 and 4) = no

In the table above, Client B (+row 5 and 6) = yes

Scope for Fast Track = IF('PV Cel Report'[Payroll+Integration]="Yes" &&....... 

Any tip is welcome

thanks,