Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 filtering on these columns.
Recent example:
Solved! Go to Solution.
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
),
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou 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
),
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThat's pretty slick, Ed!
Thank you @edhans ! This worked perfectly! Greatly appreciated. Have an excellent day!
Glad to help @cmengel
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI would use this "else if" instead of the "or"s:
else if List.Contains({"A", "B", "C", "1", "2"}, Text.ToList(Text.Start([WO_LABOR_CLASS_CODE], 1))) then "N" else if...
--Nate
If you
Hey, @watkinnc ! Thanks for the reply. I found the Text.ToList function unneccessary in this case.
Appreciate you taking the time!