Insert row when conditions met on existing data

[daniegajohnson]

Hey All,

I am looking to add new rows to my data using values from the existing rows. Their are around 600,000 rows of data at the moment. Here is a parsed down sample:

Lookup ID	Transaction Date	Type	Quantity	Net Amount	Membership Level	Membership Transaction Type	Membership Promotion Name	Membership Promotion Type	Membership Promotion Amount
123456789	12/15/19	Membership	1	95	Family	Join	Groupon Membership Discount	Discount	95
123456780	15/29/19	Membership	1	95	Family	Renew	Expired Groupon Discount	Discount	75

I would like the result to look like this:

Lookup ID	Transaction Date	Type	Quantity	Net Amount	Membership Level	Membership Transaction Type	Membership Promotion Name	Membership Promotion Type	Membership Promotion Amount
123456789	12/15/19	Membership	1	95	Family	Join	Groupon Membership Discount	Discount	95
123456789	12/15/19	Membership	1	-95	Family	Discount	Groupon Membership Discount	Discount	95
123456780	12/29/19	Membership	1	95	Family	Renew	Expired Groupon Discount	Discount	75
123456780	12/29/19	Membership	1	-75	Family	Discount	Expired Groupon Discount	Discount	75

In my head I was thinking something like Custom Row = each if [Membership Promotion Type] = "Discount" then Table.InsertRows, but I'm not sure what to do next to pull the values from the proper line (becuase I'm new here).

Any help would be greatly appreciated!

Thanks,

Dan

[v-lid-msft]

Hi @daniegajohnson ,

We can add a custom column and then expand it to the new rows to meet your requirement:

if [Type]="Membership" and [Membership Promotion type]="Discount" then {[Net Amount], -[Net Amount]} else  {[Net Amount]}

 

After change the column name and reorder the columns:

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewVNIBsvUNTfUNQUzf1Nyk1KLijMwCkDgQW5oCCbfE3MycSiDDKz8zD0i5F+WXFuTnKSBUK7hkFifnl+aVAGWRmEDdsTrRYIMMDPWBCGyJf0lGahGQNgJicwMg4Zefl0qG6YYGENOhXgGZBPSHkSVRXglKzUstB9KuFQWZRakpCjBbsVtlDvGIEapHUKwAyZki+YYIC6AqjYD+iAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Lookup ID" = _t, #"Transaction Date" = _t, Type = _t, Quantity = _t, #"Net Amount" = _t, #"Membership Level" = _t, #"Membership Transaction Type" = _t, #"Membership Promotion Name" = _t, #"Membership Promotion type" = _t, #"Membership Promotion Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lookup ID", Int64.Type}, {"Transaction Date", type text}, {"Type", type text}, {"Quantity", Int64.Type}, {"Net Amount", Int64.Type}, {"Membership Level", type text}, {"Membership Transaction Type", type text}, {"Membership Promotion Name", type text}, {"Membership Promotion type", type text}, {"Membership Promotion Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Type]="Membership" and [Membership Promotion type]="Discount" then {[Net Amount], -[Net Amount]} else  {[Net Amount]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Lookup ID", "Transaction Date", "Type", "Quantity", "Custom", "Net Amount", "Membership Level", "Membership Transaction Type", "Membership Promotion Name", "Membership Promotion type", "Membership Promotion Amount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Net Amount"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "NetAmount"}})
in
    #"Renamed Columns"

Best regards,

[artemus]

What you need to do is to add a new column that is of a table type (one column for each column you are changing, and one row no replacement, 2 rows for an insert)

Like:

Custom Row = each #table(type table [New QuantityNet Amount=number, New Membership Transaction Type=text], if [Membership Promotion Type] = "Discount" then {{[QuantityNet Amount], [Membership Transaction Type]}, {-[QuantityNet Amount], "Discount"}} else {{[QuantityNet Amount], [Membership Transaction Type]}})

Then just do a table expand on the new column, followed by delete old columns and rename new ones to old names.

[Jimmy801]

Hello @daniegajohnson 

don't get the trigger.

So whenever "Discount" is stated in the promotion type, you would like to have the same line but changed to transaction type "Discount" and the amount *-1?

Jimmy

[daniegajohnson]

@Jimmy801Yes, the trigger should be ([Type] = "Membership" and [Membership Promotion Type] = "Discount")

and then, in the new row the value for Net Amount would be equal to ([Membership Promotion Amount]*-1) from the triggered row and the the value for Type in the new row would be equal to [Memberhsip Promotion Type].

[Jimmy801 · Accepted solution]

Hello @daniegajohnson 

try out this solution. if filters the table and applys the changes requested by you. After that both tables are combined.

let
	Source = #table
	(
		{"Lookup ID","Transaction Date","Type","Quantity","Net Amount","Membership Level","Membership Transaction Type","Membership Promotion Name","Membership Promotion Type","Membership Promotion Amount"},
		{
			{"123456789","43814","Membership"," 1"," 95"," Family"," Join","Groupon Membership Discount","Discount"," 95"},	{"123456780","43828","Membership"," 1"," 95"," Family"," Renew"," Expired Groupon Discount","Discount"," 75"}

					}
	),
    ChangeType = Table.TransformColumnTypes(Source,{{"Membership Promotion Amount", Int64.Type}, {"Net Amount", Int64.Type}}),
    FilterForDiscount = Table.SelectRows
    (
        ChangeType,
        each [Type] = "Membership" and [Membership Promotion Type] = "Discount"
    ),
    DeleteNetAmountType = Table.RemoveColumns(FilterForDiscount,{"Net Amount", "Membership Transaction Type"}),
    AddedNewAmount = Table.AddColumn(DeleteNetAmountType, "Net Amount", each [Membership Promotion Amount]*-1),
    AddedNewType = Table.AddColumn(AddedNewAmount, "Membership Transaction Type", each [Membership Promotion Type]),
    Combine = Table.Combine({ChangeType, AddedNewType})
in
	Combine

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]

Thanks @Jimmy801 ! I made a few modifications, but your code worked! New rows were added with the information I need.

Thanks for answering another one of my questions!

Cheers.

Dan