URGENT HELP NEEDED: Formula with multiple conditions needed

[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

[Schwadenfeld]

Thank you! This works, thank you so much. two more things:
- could you explain me the logic of what you have done so I can understand it and replicate it myself in the future

- it sems to work, just for some cases it's not pulling the right values, do you know why? 
Example here: https://www.loom.com/share/69003b2e9a514f4fb940deb34a4e13fe

[Schwadenfeld]

my file attached here:
https://we.tl/t-xy4Px569J5

@v-yanjiang-msft 

[v-yanjiang-msft · Accepted solution]

Hi @Schwadenfeld ,

I have watched your video and got it. I modify my sample.

As you can see, for all null cells in [Advertised ASIN] of "B09H5VCT2W", should be replaced with 15(the first [Advertised ASIN] of max [14 Day Total Sales]3). Also, for all [Extracted ASIN] A, the value also should be 15.

Here's my solution.

1.Add an index column in Power Query.

2.Add a step in Advanced Editor.

#"Replace"=Table.ReplaceValue(#"Added Index",each [Advertised ASIN], each let max=List.Max(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN] and x[Advertised ASIN]<>null)[14 Day Total Sales]),AllMax=List.Max(Table.SelectRows(#"Added Index", (x)=>x[Advertised ASIN]<>null)[14 Day Total Sales]) in if [Advertised ASIN]=null and Table.RowCount(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN]and x[14 Day Total Sales]<>0))<>0 then Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=max and x[Index]=List.Min(Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=max)[Index]))[Advertised ASIN]{0} else if[Advertised ASIN]=null then Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=AllMax and x[Index]=List.Min(Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=AllMax)[Index]))[Advertised ASIN]{0}else [Advertised ASIN],Replacer.ReplaceValue,{"Advertised ASIN"})

Get the correct result:

Here's the whole M syatax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjKw9DANcw4xClfSUTIAYmOlWB0swsSKGgOxoSmmuBFu1WZgcUeokeYoPHycWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extracted ASIN" = _t, #"14 Day Total Sales" = _t, #"Advertised ASIN" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Advertised ASIN", Int64.Type}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Extracted ASIN", type text}, {"14 Day Total Sales", type number}, {"Advertised ASIN", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Replace"=Table.ReplaceValue(#"Added Index",each [Advertised ASIN], each let max=List.Max(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN] and x[Advertised ASIN]<>null)[14 Day Total Sales]),AllMax=List.Max(Table.SelectRows(#"Added Index", (x)=>x[Advertised ASIN]<>null)[14 Day Total Sales]) in if [Advertised ASIN]=null and Table.RowCount(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN]and x[14 Day Total Sales]<>0))<>0 then Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=max and x[Index]=List.Min(Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=max)[Index]))[Advertised ASIN]{0} else if[Advertised ASIN]=null then Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=AllMax and x[Index]=List.Min(Table.SelectRows(#"Added Index",(x)=>x[14 Day Total Sales]=AllMax)[Index]))[Advertised ASIN]{0}else [Advertised ASIN],Replacer.ReplaceValue,{"Advertised ASIN"})
  in
    #"Replace"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Schwadenfeld]

anyone knows the solution? thank you so much

[Schwadenfeld]

I don't know how I can upload the file itself so I have uploaded it here:
https://we.tl/t-KHwdteLfRc 

[v-yanjiang-msft]

Hi @Schwadenfeld ,

According to your description, here's my solution.

1.Add an index column in Power Query.

2.Add a step in Power Query Advanced Editor.

#"Replace Value"=Table.ReplaceValue(#"Added Index",each[Advertised ASIN], each if [14 Day Total Sales]=List.Max(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN]and x[14 Day Total Sales]<> 0)[14 Day Total Sales]) and [Index]=List.Min(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN]and x[14 Day Total Sales]<> 0)[Index])then [Extracted ASIN] else [Advertised ASIN],Replacer.ReplaceValue,{"Advertised ASIN"})

Get the result:

Here's the whole M sayntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjKw9DANcw4xClfSUTIAYqVYHaqLGlromVgQlnFENoAYTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extracted ASIN" = _t, #"14 Day Total Sales" = _t, #"Advertised ASIN" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Advertised ASIN", Int64.Type}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Extracted ASIN", type text}, {"14 Day Total Sales", type number}, {"Advertised ASIN", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Replace Value"=Table.ReplaceValue(#"Added Index",each[Advertised ASIN], each if [14 Day Total Sales]=List.Max(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN]and x[14 Day Total Sales]<> 0)[14 Day Total Sales]) and [Index]=List.Min(Table.SelectRows(#"Added Index",(x)=>x[Extracted ASIN]=[Extracted ASIN]and x[14 Day Total Sales]<> 0)[Index])then [Extracted ASIN] else [Advertised ASIN],Replacer.ReplaceValue,{"Advertised ASIN"})
   
in
    #"Replace Value"

I'm not very clear about your last condition "if no highest value for the related EXTRACTED ASIN can be found then....". Could you please explain about it, for example my sample below, for EXTRACTED ASIN A, there is no sales, then what's the expected result for A.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Schwadenfeld]

Hello! I made a short video so my problem is clear: https://www.loom.com/share/15de82e9a7014cf58e5281b011e4768a

Sample Data: 

let
Source = Table.NestedJoin(#"Sponsored Brands Campaign Repor", {"Date", "Campaign Name"}, #"Test SB", {"Date", "Campaign Name"}, "Sponsored Brands Campaign Repor", JoinKind.LeftOuter),
#"Expanded Sponsored Brands Campaign Repor" = Table.ExpandTableColumn(Source, "Sponsored Brands Campaign Repor", {"Purchased ASIN"}, {"Advertised ASIN"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Sponsored Brands Campaign Repor", "Campaign Name", "Campaign Name - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Date", "Portfolio name", "Currency", "Campaign Name", "Campaign Name - Copy", "Impressions", "Clicks", "Click-Thru Rate (CTR)", "Cost Per Click (CPC)", "Spend", "Total Advertising Cost of Sales (ACOS) ", "Total Return on Advertising Spend (ROAS)", "14 Day Total Sales", "14 Day Total Orders (#)", "14 Day Total Units (#)", "14 Day Conversion Rate", "Viewable impressions", "View-through rate (VTR)", "Click-through rate for views (vCTR)", "Video first quartile views", "Video midpoint views", "Video third quartile views", "Video complete views", "Video unmutes", "5-second views", "5 Second View Rate", "14-Day Branded Searches", "14-day Detail Page Views (DPV)", "14 Day New-to-brand Orders (#)", "14 Day % of Orders New-to-brand", "14 Day New-to-brand Sales", "14 Day % of Sales New-to-brand", "14 Day New-to-brand Units (#)", "14 Day % of Units New-to-brand", "14 Day New-to-brand Order Rate", "Advertised ASIN"}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Reordered Columns", {{"Campaign Name - Copy", each Text.BetweenDelimiters(_, "- B0", " -"), type text}}),
#"Added Custom" = Table.AddColumn(#"Extracted Text Between Delimiters", "Custom", each "B0"),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Date", "Portfolio name", "Currency", "Campaign Name", "Campaign Name - Copy", "Custom", "Impressions", "Clicks", "Click-Thru Rate (CTR)", "Cost Per Click (CPC)", "Spend", "Total Advertising Cost of Sales (ACOS) ", "Total Return on Advertising Spend (ROAS)", "14 Day Total Sales", "14 Day Total Orders (#)", "14 Day Total Units (#)", "14 Day Conversion Rate", "Viewable impressions", "View-through rate (VTR)", "Click-through rate for views (vCTR)", "Video first quartile views", "Video midpoint views", "Video third quartile views", "Video complete views", "Video unmutes", "5-second views", "5 Second View Rate", "14-Day Branded Searches", "14-day Detail Page Views (DPV)", "14 Day New-to-brand Orders (#)", "14 Day % of Orders New-to-brand", "14 Day New-to-brand Sales", "14 Day % of Sales New-to-brand", "14 Day New-to-brand Units (#)", "14 Day % of Units New-to-brand", "14 Day New-to-brand Order Rate", "Advertised ASIN"}),
#"Merged Columns" = Table.CombineColumns(#"Reordered Columns1",{"Custom","Campaign Name - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Extracted ASIN"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Portfolio name", "Currency", "Impressions", "Clicks", "Click-Thru Rate (CTR)", "Cost Per Click (CPC)", "Total Return on Advertising Spend (ROAS)", "14 Day Total Units (#)", "14 Day Conversion Rate", "Viewable impressions", "View-through rate (VTR)", "Click-through rate for views (vCTR)", "Video first quartile views", "Video midpoint views", "Video third quartile views", "Video complete views", "Video unmutes", "5-second views", "5 Second View Rate", "14-Day Branded Searches", "14-day Detail Page Views (DPV)", "14 Day New-to-brand Orders (#)", "14 Day % of Orders New-to-brand", "14 Day New-to-brand Sales", "14 Day % of Sales New-to-brand", "14 Day New-to-brand Units (#)", "14 Day % of Units New-to-brand", "14 Day New-to-brand Order Rate", "Spend", "Total Advertising Cost of Sales (ACOS) ", "14 Day Total Orders (#)"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Extracted ASIN] = "B09H5VCT2W"))
in
#"Filtered Rows"

[Schwadenfeld]

I don't know how I can upload the file itself so I have uploaded it here:
https://we.tl/t-KHwdteLfRc

[ryan_mayu]

@Schwadenfeld 

could you pls provide the sample data (not the screenshot) and the expected output?

[Schwadenfeld]

Attached here: https://we.tl/t-xy4Px569J5 
@ryan_mayu 

[Schwadenfeld]

Hello! I made a short video so my problem is clear: https://www.loom.com/share/15de82e9a7014cf58e5281b011e4768a

I don't know how I can upload the file itself so I have uploaded it here:
https://we.tl/t-KHwdteLfRc 

Sample Data: 

let
Source = Table.NestedJoin(#"Sponsored Brands Campaign Repor", {"Date", "Campaign Name"}, #"Test SB", {"Date", "Campaign Name"}, "Sponsored Brands Campaign Repor", JoinKind.LeftOuter),
#"Expanded Sponsored Brands Campaign Repor" = Table.ExpandTableColumn(Source, "Sponsored Brands Campaign Repor", {"Purchased ASIN"}, {"Advertised ASIN"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Sponsored Brands Campaign Repor", "Campaign Name", "Campaign Name - Copy"),
#"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Date", "Portfolio name", "Currency", "Campaign Name", "Campaign Name - Copy", "Impressions", "Clicks", "Click-Thru Rate (CTR)", "Cost Per Click (CPC)", "Spend", "Total Advertising Cost of Sales (ACOS) ", "Total Return on Advertising Spend (ROAS)", "14 Day Total Sales", "14 Day Total Orders (#)", "14 Day Total Units (#)", "14 Day Conversion Rate", "Viewable impressions", "View-through rate (VTR)", "Click-through rate for views (vCTR)", "Video first quartile views", "Video midpoint views", "Video third quartile views", "Video complete views", "Video unmutes", "5-second views", "5 Second View Rate", "14-Day Branded Searches", "14-day Detail Page Views (DPV)", "14 Day New-to-brand Orders (#)", "14 Day % of Orders New-to-brand", "14 Day New-to-brand Sales", "14 Day % of Sales New-to-brand", "14 Day New-to-brand Units (#)", "14 Day % of Units New-to-brand", "14 Day New-to-brand Order Rate", "Advertised ASIN"}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Reordered Columns", {{"Campaign Name - Copy", each Text.BetweenDelimiters(_, "- B0", " -"), type text}}),
#"Added Custom" = Table.AddColumn(#"Extracted Text Between Delimiters", "Custom", each "B0"),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Date", "Portfolio name", "Currency", "Campaign Name", "Campaign Name - Copy", "Custom", "Impressions", "Clicks", "Click-Thru Rate (CTR)", "Cost Per Click (CPC)", "Spend", "Total Advertising Cost of Sales (ACOS) ", "Total Return on Advertising Spend (ROAS)", "14 Day Total Sales", "14 Day Total Orders (#)", "14 Day Total Units (#)", "14 Day Conversion Rate", "Viewable impressions", "View-through rate (VTR)", "Click-through rate for views (vCTR)", "Video first quartile views", "Video midpoint views", "Video third quartile views", "Video complete views", "Video unmutes", "5-second views", "5 Second View Rate", "14-Day Branded Searches", "14-day Detail Page Views (DPV)", "14 Day New-to-brand Orders (#)", "14 Day % of Orders New-to-brand", "14 Day New-to-brand Sales", "14 Day % of Sales New-to-brand", "14 Day New-to-brand Units (#)", "14 Day % of Units New-to-brand", "14 Day New-to-brand Order Rate", "Advertised ASIN"}),
#"Merged Columns" = Table.CombineColumns(#"Reordered Columns1",{"Custom","Campaign Name - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Extracted ASIN"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Portfolio name", "Currency", "Impressions", "Clicks", "Click-Thru Rate (CTR)", "Cost Per Click (CPC)", "Total Return on Advertising Spend (ROAS)", "14 Day Total Units (#)", "14 Day Conversion Rate", "Viewable impressions", "View-through rate (VTR)", "Click-through rate for views (vCTR)", "Video first quartile views", "Video midpoint views", "Video third quartile views", "Video complete views", "Video unmutes", "5-second views", "5 Second View Rate", "14-Day Branded Searches", "14-day Detail Page Views (DPV)", "14 Day New-to-brand Orders (#)", "14 Day % of Orders New-to-brand", "14 Day New-to-brand Sales", "14 Day % of Sales New-to-brand", "14 Day New-to-brand Units (#)", "14 Day % of Units New-to-brand", "14 Day New-to-brand Order Rate", "Spend", "Total Advertising Cost of Sales (ACOS) ", "14 Day Total Orders (#)"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Extracted ASIN] = "B09H5VCT2W"))
in
#"Filtered Rows"