How to add new column that counts occurrences and calculates only when certain conditions are met

[daniegajohnson]

Hey All,

Here is a sample of the data I have:

Order ID	Type	Quantity	Amount	Show	Type ID
10926064	Ticket	1	$6.00	12 pm show	Adult
10926064	Ticket	1	$6.00	1 pm show	Adult
10926064	Discount	1	$12.00	12 pm show	100% Off
10926064	Discount	1	$12.00	1 pm show	100% Off

I am pulling this data from our database, and the reason for the duplicate discount rows is because two separate shows were purchased on the same order, and the type of discount used is for the entire order, and not on the show level. Because of that, the database attributes the discount to both shows, but cannot tell me how much of the discount went to each show. It can only tell me how much value the discount had, and what shows it was applied to. So, at the moment, if I were to convert the Amount when the row Type="Discount" to a negative number, then this order would have a value of -$12.00, instead of $0.00. I have tried to manipulate my database query to avoid this, but there is no way to.

Here is what I would like the results to be:

Order ID	Type	Quantity	Amount	Show	Type ID	Discount Count	Adjusted Discount
10926064	Ticket	1	$6.00	12 pm show	Adult
10926064	Ticket	1	$6.00	1 pm show	Adult
10926064	Discount	1	$12.00	12 pm show	100% Off	2	-$6.00
10926064	Discount	1	$12.00	1 pm show	100% Off	2	-$6.00

I would like the Discount Count column to be this kind of logic:  when Type equals 'Discount' and Type ID equals '100% Off' then count the number of occurrences of the Lookup ID, but only for the rows when those conditions are met. I don't want to count to two Ticket rows. The Adjusted Discount colum is easy enough.

Any thoughts would be greatly appreciated.

Thanks,

Dan

[Jimmy801 · Accepted solution]

Hello @daniegajohnson 

you can achieve this by appying some grouping and rejoining the result. Consider that if I would know the higher goal, a better solution may be possible. Here the example code

let
	Source = #table
	(
		{"Order ID","Type","Quantity","Amount","Show","Type ID"},
		{
			{"10926064","Ticket","1","6","12 pm show","Adult"},	{"10926064","Ticket","1","6","1 pm show","Adult"},	{"10926064","Discount","1","12","12 pm show","100% Off"},	
			{"10926064","Discount","1","12","1 pm show","100% Off"}
		}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Quantity", Int64.Type}}),
    Filter = Table.SelectRows(ChangeType, each ([Type] = "Discount")),
    Group = Table.Group(Filter, {"Order ID"}, {{"CountRowsOfDiscount", each Table.RowCount(_), type number}}),
	Join = Table.NestedJoin(ChangeType,"Order ID", Group, "Order ID", "CountRowsOfDiscount"),
    Expand = Table.ExpandTableColumn(Join, "CountRowsOfDiscount", {"CountRowsOfDiscount"}, {"CountRowsOfDiscount.CountRowsOfDiscount"}),
    AddDiscountCount = Table.AddColumn(Expand, "Discount Count", each if [Type]="Discount" then [CountRowsOfDiscount.CountRowsOfDiscount] else 0),
    DeleteColumns = Table.RemoveColumns(AddDiscountCount,{"CountRowsOfDiscount.CountRowsOfDiscount"})
in
	DeleteColumns

Copy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[daniegajohnson]

@Jimmy801I just tried copy and pasting your code, after changing some of the column names to match my actual data. I got several errors, they all reference ChangeType.

[Jimmy801]

Hello @daniegajohnson 

you have to rename your second step into "ChangeType"

Jimmy

[daniegajohnson]

Thanks @Jimmy801  we're almost there!

I would like to add an additional qualifier like this:

Filter = Table.SelectRows(ChangeType, each ([Type] = "Discount" and [DiscountName] = "100% Off"))

It's necesarry because there are certain discounts that don't require this elaborate step. There are three in total that require this calculation: 100% Off; Adjustable discount; and Groupon Tickets Discount. How can I do that?

Here's what I've got so far, which is working for the Discount Name "100% Off"

#"ChangeType" = Table.TransformColumnTypes(Source,{{"RawQuantity", Int64.Type}, {"NetAmount", type number}}),
Filter = Table.SelectRows(ChangeType, each (([Type] = "Discount" and [DiscountName] = "100% Off"))),
Group = Table.Group(Filter, {"LookupID"}, {{"CountRowsOfDiscount", each Table.RowCount(_), type number}}),
Join = Table.NestedJoin(ChangeType,"LookupID", Group, "LookupID", "CountRowsOfDiscount"),
Expand = Table.ExpandTableColumn(Join, "CountRowsOfDiscount", {"CountRowsOfDiscount"}, {"CountRowsOfDiscount.CountRowsOfDiscount"}),
AddDiscountCount = Table.AddColumn(Expand, "Discount Count", each if ([Type]="Discount" and [DiscountName] = "100% Off") then [CountRowsOfDiscount.CountRowsOfDiscount] else null),
DeleteColumns = Table.RemoveColumns(AddDiscountCount,{"CountRowsOfDiscount.CountRowsOfDiscount"})
in
DeleteColumns

[Jimmy801]

Hello @daniegajohnson 

if I got you right, try something like this

Filter = Table.SelectRows
   (ChangeType, 
    each ([Type] = "Discount" and 
   (
        [DiscountName] = "100% Off" or 
        [DiscountName] = "Adjustable discount" or 
        [DiscountName] = "Groupon Tickets Discount" or 
    )
   )
)

Hope this helps

Jimmy

[daniegajohnson]

@Jimmy801  Thanks! That worked! In case you were wondering, here is the final code:

let
    Source = OData.Feed(),
    #"ChangeType" = Table.TransformColumnTypes(Source,{{"RawQuantity", Int64.Type}, {"NetAmount", type number}, {"RefundQuantity", Int64.Type}, {"RefundAmount", type number}}),
    Filter = Table.SelectRows(ChangeType, each (([Type] = "Discount" and [DiscountName] = "100% Off" or [DiscountName] = "Adjustable discount" or [DiscountName] = "Groupon Tickets Discount"))),
    Group = Table.Group(Filter, {"LookupID"}, {{"CountRowsOfDiscount", each Table.RowCount(_), type number}}),
	Join = Table.NestedJoin(ChangeType,"LookupID", Group, "LookupID", "CountRowsOfDiscount"),
    Expand = Table.ExpandTableColumn(Join, "CountRowsOfDiscount", {"CountRowsOfDiscount"}, {"CountRowsOfDiscount.CountRowsOfDiscount"}),
    AddDiscountCount = Table.AddColumn(Expand, "Discount Count", each if ([Type]="Discount" and [DiscountName] = "100% Off") then [CountRowsOfDiscount.CountRowsOfDiscount] else if ([Type] = "Discount" and [DiscountName] = "Adjustable discount") then [CountRowsOfDiscount.CountRowsOfDiscount] else if ([Type] = "Discount" and [DiscountName] = "Groupon Tickets Discount") then [CountRowsOfDiscount.CountRowsOfDiscount] else null),
    DeleteColumns = Table.RemoveColumns(AddDiscountCount,{"CountRowsOfDiscount.CountRowsOfDiscount"})
in
    DeleteColumns

Thanks for all the help. It has been much appreciated.

Cheers.

[daniegajohnson]

@Jimmy801Thanks for the reply!

I should have been more specific, my bad. The data set I'm working with is all sales from 2019, so it's a few hundred thousand rows long. It starts with these 16 columns:

1- Lookup ID

2- Transaction Date

3- Type

4- Price Type

5- Raw Quantity

6- Program

7- Program Event

8- New Amount

9- Membership Level

10- Membership Transaction Type

11- Fund Name

12- Discounted Program

13- Discount Name

14- Refund Quantity

15- Refund Amount

16- Merchandise Name

From those 16 columns I would like to get it down to these:

1- Lookup ID

2- Transaction Date

3- Type

4- Type Identifier (new added column of if/then statements)

5- Quantity

6- Amount (new added column of if/then statements)

7- Program (new added column of if/then statements)

8- Program Identifier (new added column of if/then statements)

My ultimate goal is to get everything into a neat Excel table, so that I can put it through a pivot table, and so on...

[Jimmy801]

Hello @daniegajohnson 

to propose here a solution I need a data example, how the transformatoin exactly should work and a expected result of this data.

Otherwise I have no chance to do anything

All the best

Jimmy