Forum Discussion
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
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
- jgeddesSuper User
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- romovaroResponsive 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 ?
- jgeddesSuper 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.
- romovaroResponsive 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