Split delimited data into rows (and leave the other column blank where extra line is made)

[lisaspencer]

Hi all,

Basically, I get a report of product sales that get sent per transaction (rather than for each product individually). This makes it very hard when using excel to find out what our sales have been on each specific product. If I put the sales list through Power Query and split the column by the "," delimiter, I can put them into another row. But the other column copies the transaction value downwards, so it's adding extra value to that column that doesn't exist. Is there a way for either: the split into rows action not to copy the other column value and leave it blank? Or even figure out what the individual product was sold for, maybe using a VLOOKUP elsewhere? I won't even begin to try and figure out how many were sold based on if there was a discount or not.

Any help is appreciated!

 

[JGordon11]

The method below isn't foolproof because your data doesn't contain enough information to definitively determine whether an order was sold at discount or full price in some cases. For example, if the sales amount was 9.00 and the full price is 1.00 and the discounted price is 0.90 then you cannot tell whether you sold nine of the product at full price or ten at the discounted price.

The method below will default to full price assumption for the example case where the data is ambiguous. It would be better if your sales table had a column specifying whether the order was at full price or discount. Then there would be no ambiguity.

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Price = Excel.CurrentWorkbook(){[Name="Price"]}[Content],
    tbl = Table.AddColumn(Source, "Custom", each Text.Split([Product],",")),
    tbl1 = Table.AddColumn(tbl, "SumPrice", each List.Accumulate([Custom], 0, (s,c)=> s + Price{[Product = c]}[Price])),
    tbl2 = Table.AddColumn(tbl1, "SumDiscount", each List.Accumulate([Custom], 0, (s,c)=> s + Price{[Product = c]}[Discount])),
    tbl3 = Table.AddColumn(tbl2, "Quantity", each let s = [Sales]/[SumPrice] in if s = Number.RoundDown(s) then s else [Sales]/[SumDiscount]),
    tbl4 = Table.AddColumn(tbl3, "Full/Discount", each let s = [Sales]/[SumPrice] in if s = Number.RoundDown(s) then "Full" else "Discount"),
    tbl5 = Table.RemoveColumns(tbl4,{"Sales", "Product", "SumPrice", "SumDiscount"}),
    tbl6 = Table.RenameColumns(Table.ExpandListColumn(tbl5, "Custom"), {{"Custom","Product"}}),
    tbl7 = Table.AddColumn(tbl6, "Profit Per", each let rcd = Price{[Product = _[Product]]}, q = [Quantity] in if [#"Full/Discount"] = "Full" then rcd[Price]*q else rcd[Discount]*q),
    tbl8 = Table.ReorderColumns(tbl7,{"Product", "Profit Per", "Quantity"}),
    Result = Table.RemoveColumns(tbl8,{"Full/Discount"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Sales		Product	Price	Discount		Product	Profit Per	Quantity
2	b,c,o,a	10.00		b	1.00	0.90		b	1.00	1
3	b	1.00		c	2.00	1.80		c	2.00	1
4	c	2.00		o	3.00	2.70		o	3.00	1
5	c,o,s	10.00		a	4.00	3.60		a	4.00	1
6	c,b,o	5.40		s	5.00	4.50		b	1.00	1
7	o	10.80						c	2.00	1
8	s	4.50						c	2.00	1
9	a,s	16.20						o	3.00	1
10								s	5.00	1
11								c	1.80	1
12								b	0.90	1
13								o	2.70	1
14								o	10.80	4
15								s	4.50	1
16								a	7.20	2
17								s	9.00	2
18
Sheet2

Cell Formulas
Range	Formula
F2:F6	F2	=0.9*E2

 

[lisaspencer]

are you...... are you a genius? this is sick. I know exactly what you mean by the ambiguity and I may look into getting that but our current sales report is very lacking in information =, this is seriously amazing though, thanks so much

 

[lisaspencer]

One more question, if I wanted to add one more column in there (preferably on the left but no real problem) I'd like it to be a vlookup column that could go back to the Price table (where I would add Groups too) so for instance I would put 'Red' next to 'c' and 'r' and then 'Yellow' next to 'b' and so on. Then once the power query is loaded, it adds the group that the product belongs to as well. I've tried playing around with it myself but keep coming up with errors as I'm not really sure what I'm doing with power query yet

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Price = Excel.CurrentWorkbook(){[Name="Price"]}[Content],
    tbl = Table.AddColumn(Source, "Custom", each Text.Split([Product],",")),
    tbl1 = Table.AddColumn(tbl, "SumPrice", each List.Accumulate([Custom], 0, (s,c)=> s + Price{[Product = c]}[Price])),
    tbl2 = Table.AddColumn(tbl1, "SumDiscount", each List.Accumulate([Custom], 0, (s,c)=> s + Price{[Product = c]}[Discount])),
    tbl3 = Table.AddColumn(tbl2, "Quantity", each let s = [Sales]/[SumPrice] in if s = Number.RoundDown(s) then s else [Sales]/[SumDiscount]),
    tbl4 = Table.AddColumn(tbl3, "Full/Discount", each let s = [Sales]/[SumPrice] in if s = Number.RoundDown(s) then "Full" else "Discount"),
    tbl5 = Table.RemoveColumns(tbl4,{"Sales", "Product", "SumPrice", "SumDiscount"}),
    tbl6 = Table.RenameColumns(Table.ExpandListColumn(tbl5, "Custom"), {{"Custom","Product"}}),
    tbl7 = Table.AddColumn(tbl6, "Profit Per", each let rcd = Price{[Product = _[Product]]}, q = [Quantity] in if [#"Full/Discount"] = "Full" then rcd[Price]*q else rcd[Discount]*q),
    tbl8 = Table.RemoveColumns(tbl7,{"Full/Discount"}),
    tbl9 = Table.AddColumn(tbl8, "Group", each Price{[Product = [Product]]}[Group]),
    Result = Table.ReorderColumns(tbl9,{"Group", "Product", "Profit Per", "Quantity"})
in
    Result

PQ sales price tables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product	Sales		Group	Product	Price	Discount		Group	Product	Profit Per	Quantity
2	b,c,o,a	10.00		Red	b	1.00	0.90		Red	b	1.00	1
3	b	1.00		Red	c	2.00	1.80		Red	c	2.00	1
4	c	2.00		Yellow	o	3.00	2.70		Yellow	o	3.00	1
5	c,o,s	10.00		Yellow	a	4.00	3.60		Yellow	a	4.00	1
6	c,b,o	5.40		Blue	s	5.00	4.50		Red	b	1.00	1
7	o	10.80							Red	c	2.00	1
8	s	4.50							Red	c	2.00	1
9	a,s	16.20							Yellow	o	3.00	1
10									Blue	s	5.00	1
11									Red	c	1.80	1
12									Red	b	0.90	1
13									Yellow	o	2.70	1
14									Yellow	o	10.80	4
15									Blue	s	4.50	1
16									Yellow	a	7.20	2
17									Blue	s	9.00	2
18
Sheet2

Cell Formulas
Range	Formula
G2:G6	G2	=0.9*F2

 

[lisaspencer]

thank you so much, honestly this is so helpful!