Simple Formual needed - Power Query New Custom Column - Refer to other cells

[Schwadenfeld]

Hello,

I want to add a new custom column here with a formula. The formula output I want to reach is the following:

the formula should search for the DATE and Campaign Name and if these are equal it should attribute the SPEND (which is was able to match at least as a total here) accodingly to the ratio of the AD SALES. 

Example Outcome: Total Spend = 3.76 for this CAMPAIGN and this DATE for 4 different PURCHASED ASINS (Products).
Desired Result in Screenshot as well, the difficulty is to combine those 2 data sources. 

However solved it THANK YOU and you saved my day!!!

[m_alireza]

Hi @Schwadenfeld , I think its best if you create a new post for your other question as its supposed to be one post = one question. That will help others in the community find help should they come across the same issue as yours in the future

[Schwadenfeld]

This has been done, please let me know whenever you need more information. Thank you so much

[Schwadenfeld]

I have the following problem. I am trying to replace the values "null" in the last column". I want to exract a value under multiple conditions which are:
- Search for the HIGHEST "14 Day Total Sales Value" of the same EXTRACTED ASIN and then replace "null" with the ADVERTISED ASIN. 
- In this example I filtered already for this EXTRACTED ASIN (for example) and one the bottom we can find the highest value now the other "null" values should be replaced for this the related ADVERTISED ASIN.
- it could be tricky as sometimes there are multiple highest values and related ADVERTISED ASINs then just the first value found should be used.
- if no highest value ( no sales at all) for the related EXTRACTED ASIN can be found then the related ADVERTISED ASIN value of the highest sales of ALL EXTRACTED ASIN Values should be used

[m_alireza · Accepted solution]

Hi @Schwadenfeld ,

You can use the Group by function in power query to do the following in Advanced settings: group by Date and Campaign Name, and add new columns which sums Ad Sales and adds all rows 
Then you can perform your calculations using custom columns.

See sample advanced editor query that does all the steps for you (just copy and paste it in your power query editor, and amend as needed to match your table name): 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MNE3MjAyUtJRcgTigKL8lNLkEgUQ29BCz8QCSBvrmZspxergVuxEimJnbIoNkFU7oak2MtAzNkFWbWiAw2wXbGajqHZG86OJgZ4RimJDHIrdQEYDHYLLi8iOjgCyLfVwOxlZbRhYrTFON7iQGykgtrGZnqUZXHEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Campaign Name" = _t, #"Purchased ASIN" = _t, #"Ad Sales" = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Campaign Name", type text}, {"Purchased ASIN", type text}, {"Ad Sales", type number}, {"Spend", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Campaign Name"}, {{"Count", each _, type table [Date=nullable date, Campaign Name=nullable text, Purchased ASIN=nullable text, Ad Sales=nullable number, Spend=nullable number]}, {"sum Ad sales", each List.Sum([Ad Sales]), type nullable number}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Purchased ASIN", "Ad Sales", "Spend"}, {"Purchased ASIN", "Ad Sales", "Spend"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Ratio of Ad Sales", each [Ad Sales]/[sum Ad sales]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Attributed Ad Spend", each [Ratio of Ad Sales] * [Spend]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"sum Ad sales"})
in
    #"Removed Columns"

sample output:

[Schwadenfeld]

Thank you! This is awesome. I have another question related to this task could you might help me here as well?