Dynamically select column based on part of Header

[MichaelJSchulz]

I use Power Query to pull various columns from a large data set—the source table has nearly 400 columns.

Many of the column headers have nearly the same name with the exception of the first part, which is a type; the second part is a category.

Here are some examples of column names:

Approved Amount Foundations

2022 Foundations

2023 Foundations

PTD Foundations

YTD Foundations

Approved Amount Concete

2022 Concrete

2023 Concrete

PTD Concrete

YTD Concrete

The formatting here is only for illustation: bold is a 'type' and the italic is the category but both together is a single column name.

I am not listing all of the types or categories: the source file has seven 'types' and more than forty categories.

I need only four of the seven types and twenty of categories. For now, I have explicitely listed each and every column. What I like to do instead is have one list of types and one list of categories that is used to select the eighty columns.

Here are some of the difficulties:

In addition to those eigthty columns I also need several columns in the source file that do not have this sort of pattern, e.g. Project Name, Project Number, Project Category, etc. Again, I'm not listing all of the columns—there are about twenty additional such columns that are needed but do not follow the type-category pattern.

Also, currently I want 2022 type and do not need the 2023 type. However, on April 1, 2023, I will need to select the 2023 type and no longer select 2022 type. This would repeat each year on April 1.

There must be some way to use the current date as a reference to automatically select the appropriate type prefix to the various category columns.

[edhans · Accepted solution]

Hi @MichaelJSchulz - try this. 
First of all, here is the file. 

Here is the "table" I am working with. Blue is your source data, green is what I am returning.

Here is the code to generate the columns to keep:

let
    Source = 
        let
            varDate = DateTime.Date(DateTime.LocalNow()),
            varYear = if Date.Month(varDate) > 3 then Date.Year(varDate) else Date.Year(varDate) - 1
        in
        {Text.From(varYear)},
    FixedTypes = {"PTD", "YTD", "Approved Amount"},
    Custom1 = List.Combine({Source, FixedTypes}),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"Types"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Types", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Categories", each Categories),
    #"Expanded Categories" = Table.ExpandTableColumn(#"Added Custom", "Categories", {"Categories"}, {"Categories.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Categories",{"Types", "Categories.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"New Columns")[New Columns]
in
    #"Merged Columns"

I am determining if I should pull 2022 or 2023 in the Source line. Then I convert that to text

The FixedTypes step are the hardcoded types you always want.

Then I convert it a table, and add a new column that pulls in ALL of the category table, which looks like this:

So added to a column the way I did, which was to just type = Categories in the Add Custom Column box (it looks like 'each Categories' in the code above) returns a table like this:

Then I expanded the Categories column. That gives me a cross join of all possible columns

I then merge those two columns with a space as a separator, then convert it to a list. This list is called ColumnsToKeep

Then I use that list in my actual table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,ColumnsToKeep, MissingField.Ignore)
in
    #"Removed Other Columns"

For columns generated that do not exist are simply skipped.

You can modify the date logic and add whatever you want to the fixed list. All of that can even be tables in Excel or SharePoint list or wherever to generate this data.

If you need more help, please post data. I wasted 10min converting your typing above into something usable and had to deal with web garbage of unichar 160 - the nonbreaking space.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[AlexisOlson]

You can also compute a Cartesian product (cross join) using List.Transform.

For example, you could define ColumnsToKeep like this:

let
    Year = Number.ToText(Date.Year(Date.AddMonths(DateTime.LocalNow(), -3))),
    FixedTypes = { "Approved Amount", Year, "PTD", "YTD" },
    FixedCategories = { "Foundations", "Concrete" },
    Other = { "Project Name", "Project Number", "Project Category" },
    TypesAndCategories =
        List.Transform(
            FixedTypes,
            (T) => List.Transform(FixedCategories, (C) => T & " " & C)
        ),
    Combined = List.Combine(TypesAndCategories) & Other
in
    Combined