Custom Column Selection with Multiple Criteria

[JimSales81]

Hello,

I have a file i pull from that updates when a new period is finished.  instead of having to manually update my power Query to pull in the new column.  I want it to be dynamic.

So i want every column that has a Period Key (202401, 202212, etc.) and some others that will link to my DIM tables, like Territory, Customer, etc. 

I tried this to get the Period Keys and it works.  But i can't then add the othe columns i need.  

= List.Select(Table.ColumnNames(#"Promoted Headers"),each Text.StartsWith(_, "20"))

Would Love something like this 

= List.Select(Table.ColumnNames(#"Promoted Headers"),each Text.StartsWith(_, "20") AND text"Territory Code" AND "Customer Number"))

There has to be an easy way to add multple criteria to this M string.

Thank You!

[ronrsnfld · Accepted solution]

Simply add the List of fixed names to your Selection:

 = List.Select(Table.ColumnNames(#"Promoted Headers"), each Text.StartsWith(_,"20")) & {"Territory","Customer","etc"}
in

[WanderingBI]

I might be wrong but I believe relationsships only exist in the data model that is created in Excel or Power BI after you run your queries.

I also cannot find much about relationsships in the Power Query (M) documentation.

So I think the most efficient thing you can do is to merge the columns that you need ("Territory Code", "Customer Number") from the DIM tables (which are variables/queries in Power Query at this point) into the query where you are doing your "each if" operation in the custom column. This should not be too computing extensive and is propably faster than any custom function (i.e. loading the tables from the other queries into each row and then filtering down or something like that).