Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |