Power Query - concatenate column titles if there is a value

[gheyman]

I have twenty there columns and I and trying to create a column that will list the column title if that column in that row has a value > 0

If [1q4] >0 then "1q4"
if [5q9] >0 then "5q9"

But I don't want to do an else because if both have a value I need the value in the new column to be 1q4 and 5q9 and so on.


Here is my end goal.
I need to sum the values in 1q4 and 5q9 only for rows where there is a value in both. But the catch is I need to be able to do this in many scenarios like if there is values in 1q4 and 75q99 then sum the rows where there is a value in both.

the "1q4" is a quantity range. I have 23 columns that have the range 1q4, 5q9, 10q24......

So Im thinking that if I have one column that concatenates the title name, if they have a value, I can then create another formula that checks this new column to see if it contains the two ranges I am comparing

I realize this may be confusing, by I can have so many variations I would need to create over 200 individual tables if I did not one by one. And then I would need to append all of them with would take the query forever to run.

I am hoping that my idea if concatenation will eventually lead me to an efficient query or two.


Thanks

 

[GraH]

@gheyman, can you share a sample here? It's not that clear to me what you are after.

I 'm thinking Table.CombineColumnsToRecord is a good starting point.

 

[GraH]

Created this starting point to have a sample, hoping I got it right.

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
1	Other Column	1q4	5q9	10q24	41q3	7q3	21q3	40q3	49q3	20q3	2q3	13q3	43q3	35q3	30q3	45q3	16q3	38q3	37q3	26q3	42q3	2q20	20q99	4q41
2	a	218	441	420	324	491	210	14	175	0	119	432	212	59	261	423	489	136	320	446	384	264	178	264
3	b	485	254	0	277	448	371	447	266	40	0	397	426	281	454	91	76	111	155	304	473	488	398	146
4	c	231	394	238	322	421	54	298	41	51	60	43	188	82	324	0	0	336	496	0	121	0	0	149
5	d	475	209	17	38	270	402	29	0	0	0	0	381	50	297	175	0	321	110	470	415	277	370	381
6	e	223	439	377	368	325	305	82	95	28	44	211	121	65	247	301	304	55	12	200	181	61	75	349
7	f	134	468	159	260	37	199	0	51	148	107	255	35	368	136	14	122	445	147	475	0	23	490	314
8	g	212	99	112	483	25	43	59	445	11	140	159	201	3	341	115	45	99	0	37	195	317	484	51
9	h	91	389	420	260	329	165	442	253	444	431	176	55	138	358	131	387	4	290	313	98	125	64	399
10	i	346	207	254	345	180	156	62	442	482	387	21	310	196	159	331	350	227	462	25	315	266	65	428
11	j	75	0	484	23	473	207	256	45	29	293	197	166	309	439	186	353	279	356	155	407	427	390	365
12	k	350	250	427	421	337	0	461	23	231	388	466	480	176	145	361	280	0	117	307	287	473	0	455
13	l	498	175	496	471	374	254	307	178	0	0	0	0	0	0	163	430	0	393	278	85	56	379	249
14	m	424	24	422	311	212	420	419	334	202	261	344	97	161	145	217	439	481	355	388	449	380	109	37
Sheet2

First PQ attempt:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Columns Not zero", each
let
Record = Table.LastN(Record.ToTable(_),23),
Value_not_zero = Table.SelectRows(Record, each [Value] >0),
Cols = Value_not_zero[Name]
in
Text.Combine(Cols, ", "))
in
    #"Added Custom"