Power Query Search by Column Header EndsWith

[brawnystaff]

I currently am using a Custom Column to return TRUE/FALSE if various rows contain any matching keywords in I have in a separate list (called Keywords) in specific columns. The issue I am having is there is like 60 columns I need to check for the keyword list. However, all columns end with "EIN". Is their way to use Text.EndsWith to search all Table columns that end in EIN? Listed below is M code I have that works, but only for three of the columns. Thanks.

List.ContainsAny({[#"Subject 1: EIN"],[#"Subject 2: EIN"],[#"Subject 3: EIN"]},Keywords)

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcnEIN = List.RemoveNulls(List.Transform(Table.ColumnNames(Source), each if Text.EndsWith(_,"EIN") then _ else null)),
    KeyWords = {1..20},
    tbl = Table.AddColumn(Source, "TF", each List.ContainsAny(List.Transform(tcnEIN, (x)=> Record.Field(_,x)), KeyWords))
in
    tbl

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	A	B	C EIN	D	E EIN	F	G EIN	H	I	J	K		A	B	C EIN	D	E EIN	F	G EIN	H	I	J	K	TF
2	163	123	133	67	69	176	4	171	182	158	11		163	123	133	67	69	176	4	171	182	158	11	TRUE
3	92	189	173	82	208	188	13	203	78	111	162		92	189	173	82	208	188	13	203	78	111	162	TRUE
4	12	124	98	62	128	229	54	57	167	165	118		12	124	98	62	128	229	54	57	167	165	118	FALSE
5	196	180	45	136	191	48	103	21	190	205	3		196	180	45	136	191	48	103	21	190	205	3	FALSE
6	108	19	20	147	53	9	213	227	181	169	174		108	19	20	147	53	9	213	227	181	169	174	TRUE
7	71	49	28	29	112	226	224	121	55	106	35		71	49	28	29	112	226	224	121	55	106	35	FALSE
8	31	96	14	75	146	42	125	60	46	144	207		31	96	14	75	146	42	125	60	46	144	207	TRUE
9	97	66	15	85	105	137	216	225	80	204	206		97	66	15	85	105	137	216	225	80	204	206	TRUE
10	154	72	73	65	50	56	37	8	33	132	223		154	72	73	65	50	56	37	8	33	132	223	FALSE
11	52	193	64	194	61	135	161	68	185	116	126		52	193	64	194	61	135	161	68	185	116	126	FALSE
12	156	47	214	93	179	88	197	95	215	24	145		156	47	214	93	179	88	197	95	215	24	145	FALSE
13	209	149	231	1	202	115	201	86	211	148	159		209	149	231	1	202	115	201	86	211	148	159	FALSE
14	198	166	152	100	89	139	102	70	109	160	44		198	166	152	100	89	139	102	70	109	160	44	FALSE
15	186	218	199	51	143	77	192	36	22	150	59		186	218	199	51	143	77	192	36	22	150	59	FALSE
16	122	134	101	7	113	172	104	129	17	127	18		122	134	101	7	113	172	104	129	17	127	18	FALSE
17	23	141	200	119	117	81	157	168	210	130	5		23	141	200	119	117	81	157	168	210	130	5	FALSE
18	155	153	151	6	131	32	228	195	178	221	99		155	153	151	6	131	32	228	195	178	221	99	FALSE
19	76	2	84	120	91	220	16	94	30	142	25		76	2	84	120	91	220	16	94	30	142	25	TRUE
20	164	222	38	63	39	114	110	90	87	219	10		164	222	38	63	39	114	110	90	87	219	10	FALSE
21	27	138	212	41	58	170	177	34	26	43	74		27	138	212	41	58	170	177	34	26	43	74	FALSE
22	140	79	184	183	217	175	83	40	230	187	107		140	79	184	183	217	175	83	40	230	187	107	FALSE
23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X2:X22	Cell Value	=TRUE	text	NO
O2:O22,Q2:Q22,S2:S22	Expression	=O2<21	text	NO

 

[brawnystaff]

Thanks JGordon11. That worked

 

[goko]

tbl = Table.AddColumn(Source, "TF", each List.ContainsAny(List.Transform(tcnEIN, (x)=> Record.Field(_,x)), KeyWords))

Can you kindly elaborate this, I kind of understand what each function does, but can't seem to get my head around List.Transform(tcnEIN, (x)=> Record.Field(_,x)

 

[JGordon11]

the each keyword is equivalent to this (_)=>, that is creating a function of _.

But if you don't use each, then you are free to use any variable name you want, like (x)=> or (myVariable)=> or any legitimate variable name.

The important thing to know is what the variable relates to,

In Table.AddColumns the function variable is always a record of the table as the Table.AddColumns function steps through the rows of the table.

In List.Transform the function variable is always an element of the list as the List.Transform function steps through the elements of the list.

with regard to tbl = Table.AddColumn(Source, "TF", each List.ContainsAny(List.Transform(tcnEIN, (x)=> Record.Field(_,x)), KeyWords))

tcnEIN is a list of column names that end with "EIN"

The _ goes with the each part of the Table.AddColumns function. The _ represents a record (row) of the table Source as the function steps through the rows.

the (x)=> part is needed because I can't use each again in the List.Transform call because the _ variable is already used by Table.AddColumn. x being used in List.Transform represents an item in the list, in this case an item in tcnEIN as List.Transform steps through the elements of tcnEIN. So the List.Transform is changing from a list of column names ending in EIN to a list of items in the current row of the table in those tcnEIN columns. Record.Field(_,x) returns a field in column whose name is contained in the variable x from a record contained in the variable _.

 

[goko]

the each keyword is equivalent to this (_)=>, that is creating a function of _.

But if you don't use each, then you are free to use any variable name you want, like (x)=> or (myVariable)=> or any legitimate variable name.

The important thing to know is what the variable relates to,

In Table.AddColumns the function variable is always a record of the table as the Table.AddColumns function steps through the rows of the table.

In List.Transform the function variable is always an element of the list as the List.Transform function steps through the elements of the list.

with regard to tbl = Table.AddColumn(Source, "TF", each List.ContainsAny(List.Transform(tcnEIN, (x)=> Record.Field(_,x)), KeyWords))

tcnEIN is a list of column names that end with "EIN"

The _ goes with the each part of the Table.AddColumns function. The _ represents a record (row) of the table Source as the function steps through the rows.

the (x)=> part is needed because I can't use each again in the List.Transform call because the _ variable is already used by Table.AddColumn. x being used in List.Transform represents an item in the list, in this case an item in tcnEIN as List.Transform steps through the elements of tcnEIN. So the List.Transform is changing from a list of column names ending in EIN to a list of items in the current row of the table in those tcnEIN columns. Record.Field(_,x) returns a field in column whose name is contained in the variable x from a record contained in the variable _.

Thanks for this.