Power Query help!

[Anonymous]

Hi guys

I'm very (very) new to Power Query, trying to teach myself and have a question I'm hoping someone can help with.

I have a set of data that has 5 Columns (Account, Channel, SKU, Date and Value).

Within Account, I have 4 different items - Sales, Promotional Spend, Cost of Goods and Logistics Cost, and overall have c.30,000 rows of data.

I want to be able to calculate say, promotional spend as a % of Sales as it's own row for each eventuality (eg for arguments sake I have 10 channels, 10 SKUs and 10 dates, so 1,000 potential eventualities) - so I want a promotional spend % of sales for each of these.

I want it as it's own row so I can then pivot it in Excel to be able to show Promotional spend as a % of sales in a pivot table over time without having to use excel formulae to calculate it. 

Is that possible?!

Thanks!

Tom

[Anonymous]

Yes here you go inc the final few rows where I've added how I want the Promo % to look. Thank you!

Channel	Account	SKU	Date	Value
Amazon	Sales	DPCC2	5/1/2023	2,222.00
Amazon	Sales	DPCC2	6/1/2023	3,322.00
Amazon	Sales	DPCC2	7/1/2023	333.00
Amazon	Sales	DPCC2	8/1/2023	12,312.00
Amazon	Sales	DPCC2	9/1/2023	3,123.00
Amazon	Sales	DPCC2	10/1/2023	12,312.00
Amazon	Sales	DPCC2	11/1/2023	134.00
Amazon	Promotional Spend	DPCC2	5/1/2023	123.00
Amazon	Promotional Spend	DPCC2	6/1/2023	546.00
Amazon	Promotional Spend	DPCC2	7/1/2023	123.00
Amazon	Promotional Spend	DPCC2	8/1/2023	421.00
Amazon	Promotional Spend	DPCC2	9/1/2023	456.00
Amazon	Promotional Spend	DPCC2	10/1/2023	4,521.00
Amazon	Promotional Spend	DPCC2	11/1/2023	2.00
Website	Sales	DAC22	5/1/2023	32.00
Website	Sales	DAC22	6/1/2023	12,312.00
Website	Sales	DAC22	7/1/2023	5,324.00
Website	Sales	DAC22	8/1/2023	3,213.00
Website	Sales	DAC22	9/1/2023	1,234.00
Website	Sales	DAC22	10/1/2023	546.00
Website	Sales	DAC22	11/1/2023	234.00
Website	Promotional Spend	DAC22	5/1/2023	2.00
Website	Promotional Spend	DAC22	6/1/2023	123.00
Website	Promotional Spend	DAC22	7/1/2023	123.00
Website	Promotional Spend	DAC22	8/1/2023	53.00
Website	Promotional Spend	DAC22	9/1/2023	1.00
Website	Promotional Spend	DAC22	10/1/2023	35.00
Website	Promotional Spend	DAC22	11/1/2023	26.00
Amazon	Promo spend as % of sales	DPCC2	5/1/2023	6%
Amazon	Promo spend as % of sales	DPCC2	6/1/2023	16%
Amazon	Promo spend as % of sales	DPCC2	7/1/2023	37%
Amazon	Promo spend as % of sales	DPCC2	8/1/2023	3%
Amazon	Promo spend as % of sales	DPCC2	9/1/2023	15%
Amazon	Promo spend as % of sales	DPCC2	10/1/2023	37%
Amazon	Promo spend as % of sales	DPCC2	11/1/2023	1%

[dufoq3 · Accepted solution]

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZOxDoIwEIZfxTA3yt21qCPBByBhcDAOGBlIkBp18uklMZE/UmnrVIb78l/+7zgckvxSP22fqKSqu+Y+vLuyKHh4zYpWnLIMn6yYeZmmyVHNANkIiJIAYA2AiHd8M44TKyF/wBY3IvZHUBqdQQSI6Ml8ebMX+2htX3eL6tr0Z3fFru3mUCjb6CwKXf+fCgo0UxQKLrSJWxitaGUig1HQR+e+Od3bR4M+84K/nEjAePbrYH4j0L8Z/hPtJzZ4x0ziJ6BtUiwBGVgyXNQMgL26EpxSpi07GpsjM+f1hrHuyw9jwYGJRFFGHIlSxESy6Oft8/gC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Channel = _t, Account = _t, SKU = _t, Date = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Currency.Type}}, "en-US"),
    PivotedColumn = Table.Pivot(ChangedType, List.Distinct(ChangedType[Account]), "Account", "Value"),
    Ad_Ratio = Table.AddColumn(PivotedColumn, "Ratio", each [Promotional Spend] / [Sales], Percentage.Type)
in
    Ad_Ratio

[dufoq3]

Hi @Anonymous, it is possible, but could you privide sample data as table so we can copy/paste and expected result based on sample data?