Forum Discussion

cmengel's avatar
cmengel
Advocate II
5 years ago
Solved

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...
  • edhans's avatar
    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
            ),