How to apply countif logic in an if statement in a calculated column

[Anonymous]

Hi all.

I have searched for an answer to this and haven't managed to find the exact solution to my problem, so hopefully someone can help.

I currently have a table set up that looks like the below but currently without the yellow column.

It represents a table of customers and for each month it contains a single row per unique combination of customer, month and category 1, category 2 or category 3. What I want to do is add a column that says when, for each customer and month combination, category 1 is found, that customer is in category 1 regardless of whether there are also category 2 and category 3 rows for that same customer and date combination. If not, then category 2 takes precedence, and then finally category 3.

I can do it in excel:

=IF(COUNTIFS([Unique ID],[@Customer]&" - "&TEXT([@Month],"dd/mm/yyyy")&" - Category 1")>0,"Category 1",IF(COUNTIFS([Unique ID],[@Customer]&" - "&TEXT([@Month],"dd/mm/yyyy")&" - Category 2")>0,"Category 2","Category 3"))

...which is what the result in the yellow column shows.

How can this be achieved in Power Query?

Thanks

Michael

[Anonymous · Accepted solution]

could group the table on the "Customer" column, and use the "Min" aggregatios as well as the "All Rows".

You can add these steps in the formula bar:

= Table.Group(NameOfPriorStep, {"Customer"}, {{"Least Category", each List.Min([Category]), type nullable text}, {"Details", each _, type table [Customer =nullable text, Month = nullable date, Category = nullable text, Unique ID =nullable text]}})

Followed by:

= Table.ExpandTableColumn(NameOfPreviousStep, "Details", {"Date", "Category", Unique ID"}, {"Date", "Category", Unique ID"})

That should do it!--Nate

[Anonymous]

Hi again. I just tried this. It was 95% there - had to include the month in the group by as well as on the first try every month got the minimum for the customer - not the customer and month combination. Also had to change my categories to just integers for the minimum to work, but I just created a table, mapped those integers to my category names, did a merge query and got the names coming through. Long story short - it worked!!! Thank you very very much for that.

This is what I got in the end:

#"Table Group1" = Table.Group(#"Added Custom", {"Parent_Account_Master_Id", "Contract Line Item.Month end"}, {{"Least Category", each List.Min([System category]), type nullable text}}),

Parent_Account_Master_Id is Customer in my example, Contract Line Item.Month end is Month, System category is Category.

[smpa01]

Can you please provide some sample data?

[Anonymous]

Apologies as I am new to the forum - I put together that excel table as an example as the data I'm using is commercially sensitive. Is there something else beyond the example in that picture you are meaning?