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 ),
edhans
5 years agoCommunity Champion
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
),