Forum Discussion
Solved
Comparing rows - Need to match column values between rows as criteria for new column
Hi, I have a table with three columns (BRAND, LOCATION, VOLUME). Below is a table that explains what I'm trying to do: So in this example, If "PRIVATE LABEL" exists in the same ...
Try as a new column
if(isblank( maxx(filter(table,table[location] =earlier([location]) && table[Brand] = "PRIVATE LABEL" && earlier(table[brand])="OUR PRIVATE LABEL"),table[VOULME])), table[VOULME],0)Might have to do some changes.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Hi jlankford ,
You could try to use below M code to see whether it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClDSUQoHYkMDA6VYHQjfA4iNkPjeIHlTCN8Rqh4m7whVD5X2R5hnihAAKTA0gQg4oRngBLUAaoAzzD6QdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Brand = _t, Location = _t, vol = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Location", type text}, {"vol", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Location"}, {{"combine", each Text.Combine([Brand], ","), type text}, {"all", each _, type table [Brand=text, Location=text, vol=number]}}),
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Brand", "vol"}, {"Brand", "vol"}),
Custom1 = Table.ReplaceValue(#"Expanded all", each [combine] , each if Text.Contains([combine],"OP") and [Brand]="P" then 0 else [vol], Replacer.ReplaceValue, {"combine"}),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"combine", Int64.Type}})
in
#"Changed Type1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.