## Formula Help

## Formula Help

(OP)

Hi

Needing help with a formula.

Table worksheet is the combination of requirements.

Need a formula in the Data worksheet to return the outcome.

Sample file attached.

Thanks,

Arv

Needing help with a formula.

Table worksheet is the combination of requirements.

Need a formula in the Data worksheet to return the outcome.

Sample file attached.

Thanks,

Arv

## RE: Formula Help

Just a few general comments regarding the criteria in the Table sheet.

If you are attempting to construct a table that yields a single row for any set of criteria, you have problems when any single two criteria in Count or Month for instance are

>=4and<=4because in either criterion, the value of 4 would be TRUE, yielding 2 rows ambiguously.Rather,

>=4and<4or>4and<=4.In the Month column you also have

>12&<12, which would exclude 12, which is not a logic error if you intended to exclude 12.Then you have criteria in Department, of NO VALUE,

A001or<>A001. Well NO VALUE is included in <>A001???Regarding a formula, I think "yer up the crik." IMNSHO, you'll need a VBA routine that can check for the existence of some OPERATOR text so that the proper OPERATOR can be used in an equality. I'd write a User Defined Function. Maybe I'll work on that tonight,

but I'll need clarification on the ALL logic problems before a solution can be ventured.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Count is the number of transactions incurred by the customer.

Month is the aging period.

In the month column, the reason why there is >12 & <12 is because of the need to differentiate those agreement status with N/A but falls within 12 months and those agreement status with N/A but falls outside 12 months. Based on the return comments, I will then need to do a sumif as they each will have a different course of action.

Department

criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001?

Nope, anything which is <>A001 will have a different value other than No Value. E.g it can be A002, A03, A004, etc

Thanks,

Arv

## RE: Formula Help

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Count has <=4 or >=4

## RE: Formula Help

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

What can we do to cater for this scenario>

## RE: Formula Help

Count of 4 and a Month of 3 will ONLY return

Outcome5.If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return

Outcome9. Just want this to be clear.And, once again to be clear, Month of 12.00 will return no outcome for Type Credit Note or Payment.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

There are multiple combinations with Type, Status, Department, Count and Month.

There are 3 criteria as well as 4 and 5 criteria.

If:

If Type = Credit Note and Agreement Status = N/A and Month >12, then Outcome 18

If Type = Credit Note and Agreement Status = N/A and Month <12, then Outcome 19

If Type = Payment and Agreement Status = N/A and Month >12, then Outcome 20

If Type = Payment and Agreement Status = N/A and Month <12, then Outcome 21

If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return Outcome9. Just want this to be clear. > Sorry, not too sure what you meant by this.

Thanks.

## RE: Formula Help

...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.

Those 3 are:

...because the proper way of coding an expression is

Either...<=x or >x

Or<x or >=x

But in no case is it proper to put the

EQUALinto both theGREATERandLESSER! That introduces AMBIGUITY!ONE way of correcting this might look like this...

Do you acknowledge that for the criteria from any row in Data...

...that there are, according to the criteria in Table, 0 Return Comments that match. This may be exactly what you intend. I only bring this up, because of the error you introduced and continue to ignore in the Count and Month above.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

I created a List sheet that has each unique occurrence of each criteria in Table, broken out into data parts. This is in order to identify the Crit1 value to lookup in Table criteria.

It also identified the ambiguities as you supply values in the YELLOW shaded cells.

As I stated, first step toward an approach to solving your question.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Type: Invoice

Status: A2

Department: A002

Count: 4

Month: 3.00

...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.

Those 3 are:

Type Status Department Count Month Return Comments

Invoice A2 <> A001 <=4 <=3 Outcome5

Invoice A2 <> A001 >=4 >=3 Outcome9

Invoice A2 <> A001 <=4 >=3 Outcome13

Damn it, this is much clearer now. All 3 criterias produces the same outcome based on the example from data that you provided.

Including criteria from column A to C, the aim is to include:

- Count More than 4 or less than 4

- Transactions that are more than 3 months or less than 3 months

Not sure how to handle if we have the following situation whereby Count = 4 and Month = 3.

Should we include another criteria with only "=" instead of <= or >= ?

ONE way of correcting this might look like this...

Type Status Department Count Month Return Comments

Invoice A2 <> A001 <4 <=3 Outcome5 - What happens when the transaction = 4

Invoice A2 <> A001 >=4 >3 Outcome9 - What happens when Month = 3

Invoice A2 <> A001 <4 >3 Outcome13 - What happens when Month = 3

Ah.....The month criteria should be <=12 or >=12. The intent is to pick up if type = Payment, status = N/A and Month is within 12 Months or Outside 12 Months.

This will apply to Row 19 to 21 on the Table worksheet.

Type: Payment

Status: N/A

Department:

Count:

Month: 12.00

## RE: Formula Help

This might be a good example...

So does that mean...???

If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month.

So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.Now you might apply a similar logic ...

Count: <=4 or >4

Month: <=3 or >3

These criteria would give you unambiguous results

As I studied your Table, I surmised (given the 3 sets you posted) that you might have 4 sets of criteria in Count & Month like thus...

...maybe?

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month. So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.

Thanks.

## RE: Formula Help

1. Up to and including 12 months Outside 12 months

<=12 >12

Or

2. Within 12 months 12 months Outside 12 months

<12 =12 >12

Or

3. Up to 12 months 12 months and greater

<12 >=12

## RE: Formula Help

Now when you put a criteria for Month, there are 2 possibilities...

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Thanks.

## RE: Formula Help

My approach would be to use those 5 Crit1 values in a SUMPRODUCT() on Table values, similar to what I did in List

I might be able to do more tomorrow, but I have on line church and then working on face masks for folks around me.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Enjoy the weekend :)

## RE: Formula Help

I'm having trouble, I think, with EMPTY cells. But here's where I am. There are still lots of problems but I can't spend any more time on it. I'm part of the way there but I can't seem to find a composite solution.

Maybe someone else has a better approach. I might have blinders on. It's very klunky, but I can't see another way.

Play around with it.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Hopefully, someone else can provide further insights.

## RE: Formula Help

Again I stress that your Data does not test ALL the criteria AND your test Data has ILLEGAL combinations.

I had to ADD 4 new rows that were not accounted for where Count is >4 and Month is <=3.

Let me know what you think. As it continues to simmer in my skull, I may come upon a less cumbersome solution.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Outcome7

Outcome13

Outcome14

Outcome15

Outcome22

Outcome24

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Correct, data might not have tested all the combinations as i just pieced it together.

The primary objective was seeking assistance with the formulas based on the criteria.

I assume that your formula will incorporate those outcomes which were not in the data.

Thank again for your help, will revert back and let you know how i go.

## RE: Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Wow, you're great grandfather now :)

Thank you so much for looking into this and finding the solution. Yep, i noticed that there are 25 columns of formula, phew.............

From the bottom of my heart, You never stopped impressing me (not the first time that you have given me solutions).

Let me plug the numbers and formulas into real data and will keep you informed.

## RE: Formula Help

I tried replicating formulas into my sheet then also have a go at your s/sheet.

To be honest, i have not yet tested all scenarios.

Say for one of the scenario, if type = Invoice and Status = N/A, it will give outcome 26.

But, if there are values in Department, Count or Month, it will give incorrect result.

Pls kindly advise.

Thanks,

Arv

## RE: Formula Help

Has anything changed in the Table or List tables? If so exactly what?

As to your specific question

...that row in your Table only has Type and Status. No other Column values are defined. Therefore, you get an error. That's how it works. Earlier I asked you this question... You answered...

...and I took that to mean that ANY NO VALUE(empty) means NO VALUE ALLOWED.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

I have not changed anything just having a try at the file you sent.

It did not give error just incorrect outcome.

## RE: Formula Help

NO VALUE

A001

<> A001

...and from your reply it was clear that NO VALUE was "Nope," not included in <> A001. Therefore, I take that to mean that NO VALUE PERMITTED.

It might be helpful to disclose what the "incorrect outcome" was.

BTW, I see an Outcome26 on the Data sheet Workbook I uploaded last

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Sorry for the confusion.

## RE: Formula Help

Here's the corrected formula for Data column Outcome

H3:

=IF(SUMPRODUCT((tCRIT[Type]=J3)*(tCRIT[Department]=K3)*(tCRIT[Count]=L3)*(tCRIT[Month]=M3)*(tCRIT[Status]=N3)*(tCRIT[Seq]))=0,"",INDEX(tCRIT[Return Comments],SUMPRODUCT((tCRIT[Type]=J3)*(tCRIT[Department]=K3)*(tCRIT[Count]=L3)*(tCRIT[Month]=M3)*(tCRIT[Status]=N3)*(tCRIT[Seq]))))_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

## RE: Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

If I have any values in other columns, the incorrect outcome is outcome 1

On the Data worksheet, Invoice, Status, Department, Count and Month will always have a value.

On the Table worksheet, i left some of the Department, Count and Month fields empty because those are the only criteria that needs to be met to return the Return Comments.

Example - Row 26 - If Type = Payment, Status = N/A and Month <=12, then return Outcome 25 (irrespective of the values in Department, Count and Month).

Likewise with the other scenarios on the Table worksheet.

Is there a formula to tackle this or do i need to expand the criteria on the Table worksheet?

Apologies that i have been a bit ambigous with the information passed on to you.

Thanks,

Arv

## RE: Formula Help

Please state the criteria rules that are presented in Table. In what cases is an empty cell a criteria or not.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Table worksheet:

- All the "empty" cells are not a criteria.

- I have not put in any values in those empty cells because there may be a mixture of values. As long as those non-empty cells criteria are fulfilled, it is to return the expected return comment

Thanks,

Arv

## RE: Formula Help

Need a formula to...

Include (Type, Status, Department, Count, Month) When (Type, Status, Department, Count, Month) NOT ISBLANK Else

Include (Type, Status, Month) When (Type, Status, Month) NOT ISBLANK AND (Department, Count) ISBLANK Else

Include (Type, Status, Department) When (Type, Status, Department) NOT ISBLANK AND (Count, Month) ISBLANK Else

Include (Type, Department) When (Type, Department) NOT ISBLANK AND (Status, Count, Month) ISBLANK Else

Include (Type, Status) When (Type, Status) NOT ISBLANK AND (Department, Count, Month) ISBLANK

Its just tedious work to get it right. You have to get the AND of the BLANK right with the NOT BLANK so you Include the NOT BLANKs in Each SUMPRODUCT. The objective is to get all that into one garhunkulous formula that you can COPY into each row of Output.

But...what I stated in my first post, "I'd write a User Defined Function," at this juncture of complexity, seems the odds on favorite IMNSHO. I mean I wore myself out just writing the logic in this reply and I'm pushin' 80. Gotta catch my breath and do some Omphaloskepsis.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

25 Type, Status, Month

17 Type, Status

31 All 5

3 Type, Department

Notice I have 6 new columns on Data to help with which cells have data or not.

Observations:

Type is the only field that has data in every row.

Count only occurs when all other fields have data.

Status N/A only has Month data or not.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Date Type Amount Department Count Month Status

Apologies that i have omitted them.

For simulation, you can add any values in those empty cells.

There are also 5 combinations.

Refer to Outcome 4 to Outcome 19.

Type Status Department Count Month

Thanks.

## RE: Formula Help

I do totally appreciate your help with this matter.

Thank you so much. Let me have a go with the latest file that you sent through.

Regards,

Arv

## RE: Formula Help

## RE: Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

See attached. I have tried to go through line by line to ensure that table and data provided is as accurate as can be.

I have gone through the full set of data which i didnt previously (laziness).

There are some scenarios which i have yet to determine where / how to categorize.

(highlighted in yellow on the Data worksheet). Do i have to categorize them now or would it be difficult be add them back in later?

I.e.

If Type = Credit Note or Payment or Refund or NULL

And Department = NULL

And Status = A1 Or A2 Or C Or S Or U

If the above has to be categorized now, let me know and I will double check the requirements for those.

Let me know if something is amiss.

Thanks again,

Arv

## RE: Formula Help

## RE: Formula Help

Type = Payment: the way I'd interpret that

Status must be N/A. However, in Data, there are lots of yellow Payment with BLANK Comment andlots of other Status values.???

Here's what is needed, for instance to be complete...

If Type = Credit Note or Payment or Refund or NULL

And Department = NULL

And Status = A1 Or A2 Or C Or S Or U

Then Outcome = x

Else If ....

And Status = N/A

And Month <=12

Then Outcome = y

Else If ...

And Month >12

Then Outcome = z

...for every category.

This is DEFINITELY something that needs a UDF in VBA. I would put the criteria into a table and interpret in VBA. You don't have the kind of logic that lends itself to a formula readily.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

## RE: Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Thanks.

## RE: Formula Help

I have removed those yellow records. They should not exist.

Thanks,

Arv

## RE: Formula Help

Is everything ok or not?

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Do you mean to put these data into your file?

Thanks.

## RE: Formula Help

Hence, I have gone through line by line and manually inserting the expected outcome.

Let me know otherwise if i am to be using one of the files that you uploaded.

## RE: Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

H3:

=Outcome(B3,D3,E3,F3,G3)...and COPY/PASTE down.

## CODE

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Thanks for the UDF. I have ran the formula through. Some of the output is incorrect.

I have attached the workbook for your review.

## RE: Formula Help

Sorry! I couldn't run a check, which I should have done.

Check out the corrected original. There were more than S. COPY n PASTE the whole thing.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

## RE: Formula Help

I'd prefer to drive the code from data rather than having the data embedded in the code. I'm giving that some thought. If I get an epiphany, I'll post that approach.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

COPY n PASTE again. Refund & Invoice in the N/A part.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

I have tried the new code and this time, it has reduced the unmatched quite a bit. There are still some records that are still coming up with incorrect output.

Have attached the file.

Thanks,

## RE: Formula Help

Be careful of making an seemingly empty cell with a SPACE. That's why the last two outcomes did not appear.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

## RE: Formula Help

I finally managed to run through the scenarios and looks good so far.

I will try a few other scenarios

Thank you so much for your help with this matter. Never failed to provide a solution.

## RE: Formula Help

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Tablesheet. This table is essentially a copy of columns A to E of the same sheet with an added column headedOutcomewhich contains what I hope you're looking for.Where there is a blank cell in any of the columns it means include

anyvalue from the same column of theDatasheetā¦exceptfor theTypecolumn which includesonly blanksfrom theDatasheet in that column.I've added a couple of bits of data to the

Datasheet in cells F496 and F1045 to demonstrate what happens when more than one comment exists for a given set of criteria; they're listed.All you need to do is right-click the new table and choose

Refresh.## RE: Formula Help

The objective is to use the Table data to fill Outcome on the Data sheet, based on 5 criteria.

Is that what "Refresh" does?

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

(The only good thing about it then, is that it could be used to check that the correct procedure or formula works properly!)

It was good educational though. If I get the inclination I'll try doing it the right way round.

## RE: Formula Help

Datasheet with theOutcomescolumn added. You just need to right click a cell in that table and chooseRefresh.I've removed the

Commentscolumn and annotations from the table on theTablesheet.You can update/extend/amend either or both source tables before refreshing the results table at cell H1.

It takes a second or two to refresh and I think it can be speeded up, if so I'll update.

## RE: Formula Help

However, I'm interested in your solution, using Power Query. Could you explain the steps for arriving at your solution? I've never used this great feature of Excel.

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

However, I have a problem with that, to do with data integrity; The result looks linked because it's in the same block of cells, but these are in fact two independent tables. If you were to sort one, the other wouldn't sort in parallel. It get's worse - if you were to sort the Power Query table, with Excel's default settings for query tables, it would get sorted in the same way immediately after refreshing every time, so the data is not likely to correspond - you can switch that setting off by right clicking the table, choosing

Table|Edit Properties, and unticking the box labelledPreserve column/sort/filter/layout, so it's all a bit of a faff.With the passage of timeā¦

Edit: I'm struggling to attach a file as before, so here's the link I copied manually:

https://files.engineering.com/getfile.aspx?folder=...

## RE: Formula Help

Datatab of the ribbon and click theQueries and Connectionsicon in theQueries and Connectionssection, aQueries and Connectionspane will appear, then if you choose theQueriessection of that pane you'll see 5 queries. Only the first 4 are needed.The

DataTablequery is short and simple; if you double-click it, thePower Query Editorpops up, and in theApplied Stepssection on the right usually the last step is selected. You can click on any step to see what's happened up to that step. The three steps areSource, which just grabs the table from the sheet, thenTrimmed Textwhich removes any trailing and leading spaces from all the cells in all the columns (in the same way as the vbaTrimcommand, it only removes those spaces and doesn't convert multiple spaces between words to single spaces as the worksheet functionTRIMdoes), then the third step changes the data type of the columns to an appropriate type for later processing. That's the Data table spruced up.Now to do the same, plus a bit, to the other table which I've called

Categories. When you bring this up in thePower Query Editor, the third step isSplit Column by Character Transitionwhere I've chosen to split the contents of theDepartmentcolumn where the text changes from being any one '=','<','>' to not being one of those three characters and I've named the columnsDeptOperandDeptValuefor obvious reasons and to make it clearer to us humans what's in those columns when we refer to them later. You can probably gather from the formula/function bar (which you can expand and scroll through just like the regular Excel formula bar) that the bit which decides how the splitting is done is:## CODE -->

Trimmed Text, trims the text in theDeptValuecolumn since the data in that column mostly had a space between the '<>' and the department name.The next two steps do much the same thing to the

CountandMonthcolumns, while the final step changes the data type of several of the columns. So that's that.I've put versions of these two tables on the sheet

Sheet1of the attached - that sheet isn't needed so can safely be deleted.Skipping to the fourth query called

Result, this is a very short one consisting of grabbing the earlier prepared DataTable query and invoking a custom function. Dead easy. Of course, the donkey work is carried out by that custom function, so let's have a look at it:It's called

fnFindOutcomes. If you double-click on it to bring it into thePQ Editoryou'll be faced with an unhelpfulEnter Parametersdialogue. If instead you right-click it and chooseEdityou get a similar dialogue and a single step. This is because it's a function. You can see the entire code in the formula bar or you could click onAdvanced Editorin theHometab to see the steps there but you won't see the results of each step as a developing table. You will see the first line which is:## CODE -->

In order to be able to see the developing steps I've duplicated the function in a query called

DEVfnFindOutcomes, so if you bring that up in thePQ Editorinstead you'll see all the steps. In fact you'll see more steps because the first 5 are just establishing the parameters/arguments manually.At first opening you'll see just Outcome 29 because that's the final step of this query. If you click on any of the first 5 steps (all beginning 'my'ā¦) you'll see the values used (some strings, some numbers) in the formula bar and you can edit them there, although it's not so obvious how to put an empty string in so it's safer to edit them in the first lines of the code manually by going into the

Advanced Editor:## CODE -->

The first real step after establishing parameter values is the

Sourcestep which just grabs theCategoriestable from theCategoriesquery. The nextReplacedValuestep does something I really should have done in theCategoriesquery and brings in the value from theDeptOpercolumn to theDeptValuecolumn if theDeptValuecolumn is null (there's only one row (3) this happens in).The next 5 steps, all names ending in ā¦'Filter', filter the table on the 5 parameters, selecting only rows from the table using the built-in function List.AnyTrue() [the

StatusFilterstep is an exception, it was too simple] which works by returningTrueif any or more of the elements in the list (in curly brackets{}) isTrue.So in that list I put some tests, and if any of the tests return true, that row is kept. In the

DeptFilter,CountFilterandMonthFiltersteps I separated each element of the list with a carriage return to help me with the logic.At the end of these filtering steps I may be left with a completely empty table, a table with one row (the ideal) or a table with several rows.

I remove all the other columns except for the outcomes column (called

Column1) in stepRemovedOtherColumns, convert that into a list in theColumn1step and finally concatenate that list using comma-space as a delimiter to create a string, being the output of the custom function.## RE: Formula Help

arvarr,Was your issue resolved? Did

p45cal'smethod work for your needs? Or did you go with anything else?I got real curious on this one when I notice 74 replies and no stars given anywhere.

I know PowerQuery can be super powerful and the other PowerBI tools MS has introduced in recent years, but I've yet to do much with them, myself. I played around with them a little just before being laid off at a place where it made a good fit. Where I am now, it isn't quite as useful currently, but perhaps one day.

I'm still curious, though. Would like to know the final outcome on this one, and thanks to

p45calfor sharing the examples via links for review. Will make it easier to glance over it."But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

## RE: Formula Help

I went with Skips solution. Haven't had the chance to try out p45cal's solution. Will give that a shot too.

Lots of stars has been given. Not sure when you mention no stars?

Thanks everyone for helping out.

Much appreciated,

Arv

## RE: Formula Help

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

## RE: Formula Help

Sorry, had to revisit this thread.

I need to incorporate an extra requirement.

There will be another column called Amount.

Extra Criteria:

If amount is >=-10 <=10, then Outcome 40.

(As long as amount is >=-10 and <=10, then give Outcome 40.

Thanks,

arv

## RE: Formula Help

latest copy.Don't know if I have it.BTW, the latest copy I uploaded to you has 29 Outcomes.

Where did 40 come from?Might be better to start a new thread with all new information, explanations, examples.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Here's the file.

Ah...40 was just a random number. the latest copy that you send certainly was up to 29 outcomes.

I have inserted the column with amount and updated table criteria to outcome 30.

Thanks,

arv

## RE: Formula Help

However, now you have undefined Status values that don't resolve to any outcome. Good luck!

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Formula Help

Regards,

arv