Forum Discussion
cmengel
5 years agoAdvocate II
Use Text.StartsWith and List.Contains to efficiently build custom columns
Hi! Has anyone figured out the best way to use List.Contains in combo with Text.StartsWith in PowerQuery? I create custom Y/N columns in PQ to make my DAX measures easier to write by filterin...
- 5 years ago
You can just use this formula cmengel if I am reading your requirements correctly:
List.Contains({"A", "S"}, Text.Start([Column1], 1))That returns a true or false if the text in column1 starts with an A or S, but not an R. So to make it part of your overall function:
#"AddedEXPENSE" = Table.AddColumn( AddedALLOWED, "EXPENSE", each if // Explicitly define EXPENSE codes List.Contains( { "3L", "3K", "3O", // letter "oh" NOT ZERO!!! "3A", "3E", "3G", "3B", "3F", "3M", "3S", "3J", "3H" }, [WO_LABOR_CLASS_CODE] ) then "Y" // Explicitly define NON-EXPENSE codes else if List.Contains( { "3C", "3I", "3V", "3P", "3Q", "3N", "3W", "3X" }, Text.Start([WO_LABOR_CLASS_CODE], 2) ) then "N" else if [WO_LABOR_CLASS_CODE] = "NON_LABOR" then "N" // Catch items that are not explicitly defined or mapped else "CLARIFY", type text ),