Conditional selection of rows in power query.

[6677028]

I have a table of totals data. Now I want to divide the data table into many subgroups such that .
Condition: 18.5 < Total Weight < 20 and Total length < 20.

 

[jdellasala]

Just as a prelim, are the totals here correct:

Book1
	A	B	C
1	ID	Wei ht	Len h
2	A	4	3
3	D	5.5	4
4	F	10.8	2
5	H	11	6
6	G	0.5	5
7	U	4	3
8	Y	5	2
9	I	6.5	4
10	w	2	5
11	Q	5	6
12	E	3	2
13	R	4	5
14	T	6	6
15	O	1	4
16	p	3	3
17	s	4	5
18	J	5	3
19	K	2	5
20	L	3	6
21	z	2	2
22	TOTAL:	85.3	79
Sheet1

Cell Formulas
Range	Formula
B22:C22	B22	=SUM(B2:B21)

They are not. Can you tell why without the actual worksheet. No. However, this might give you a clue:

(Had to use a screenshot! XL2BB wasn't carrying the formatting properly).
Just by looking, you KNOW that the 2's in the bottom row are Text, not Numbers, and therefore need to be fixed. Doing alignment on numbers hides what is built into Excel to warn you that what might look like numbers are not. It's also better to use ROUND rather than formatting as sums can get messed up since they read the VALUES, not the visually rounded numbers.
What you want to do is to pull the table in, and then REFERENCE it 3 times.
Otherwise, I'm kind of stumped how to approach this, but look forward to seeing if someone has the answer!

 

[GraH]

Add a column that includes a conditional column in which you build your if then else logic.
Then reference this query and filter it on that column. After filtering, remove this scenario column. Repeat for each scenario.

 

[6677028]

Just as a prelim, are the totals here correct:

Book1
	A	B	C
1	ID	Wei ht	Len h
2	A	4	3
3	D	5.5	4
4	F	10.8	2
5	H	11	6
6	G	0.5	5
7	U	4	3
8	Y	5	2
9	I	6.5	4
10	w	2	5
11	Q	5	6
12	E	3	2
13	R	4	5
14	T	6	6
15	O	1	4
16	p	3	3
17	s	4	5
18	J	5	3
19	K	2	5
20	L	3	6
21	z	2	2
22	TOTAL:	85.3	79
Sheet1

Cell Formulas
Range	Formula
B22:C22	B22	=SUM(B2:B21)

They are not. Can you tell why without the actual worksheet. No. However, this might give you a clue:
View attachment 80797
(Had to use a screenshot! XL2BB wasn't carrying the formatting properly).
Just by looking, you KNOW that the 2's in the bottom row are Text, not Numbers, and therefore need to be fixed. Doing alignment on numbers hides what is built into Excel to warn you that what might look like numbers are not. It's also better to use ROUND rather than formatting as sums can get messed up since they read the VALUES, not the visually rounded numbers.
What you want to do is to pull the table in, and then REFERENCE it 3 times.
Otherwise, I'm kind of stumped how to approach this, but look forward to seeing if someone has the answer!

Thank you,
I forgot to update.
You can see the attached file

Book11

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[jdellasala]

Thank you,
I forgot to update.
You can see the attached file

Book11

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Appreciate the effort, but I don't know how to approach this. I'll think about it more, but it requires a lot of permutations (Total Weight is greater than 18.5 AND less than 20). I don't think Power Query is the answer here, but rather a well crafted Array formula. I'll see if I can come up with anything, but right now it's beyond me!

 

[GraH]

Do forget all about my earlier reply #3. I now understand it is about selecting a set of rows which totals matches the given criteria. Like said in #5 by @jdellasala, beyond me too! Sorry for misguiding.

 

[6677028]

Appreciate the effort, but I don't know how to approach this. I'll think about it more, but it requires a lot of permutations (Total Weight is greater than 18.5 AND less than 20). I don't think Power Query is the answer here, but rather a well crafted Array formula. I'll see if I can come up with anything, but right now it's beyond me!

That's right, it requires a lot of permutations

 

[6677028]

Add a column that includes a conditional column in which you build your if then else logic.
Then reference this query and filter it on that column. After filtering, remove this scenario column. Repeat for each scenario.

You can do it for me. We can add extra columns. But I have no idea how to add extra column with conditional statement.
Thank!