Comparing stores

Hi Anonymous ,

Regarding it should check it's gross revenue and then flag those stores too having gross revenue close to K071002's, I'm not sure how you want to make the comparison specifically, so I directly calculated the difference between the other % of coupons and K071002's % of coupons.
Put all of the M function into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLC4MwEIT/SvCsIZuseVxbj4VCr9aDWGmFEouP/v4maquihQSG7MzHTtI0uJTv0vYlgSAMlAZmqNBOsuFm4Wzg7gU4ShTUeDOwGIFRQK9NrIymGlYJ8eN8WUl1r7r8SSaHT0oQMVXSSRELJUe2gwmKZsgc6+jQ5PZGTnWRd1Vt20Wc7a6Km4kT0xmR/ctzrj1jXJKkaou6t11Ld21jWcUp4yvmNPaP0vCxA6Brg15po1wFXDq3//FvERKRbe1N+tw9ymYOrefZBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, K071002 = _t, #"036235" = _t, #"040699" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"K071002", type number}, {"036235", type number}, {"040699", type number}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type number}, {"Column4", type number}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"Revenue 1", type number}, {"Revenue 2", type number}, {"Revenue 3", Int64.Type}, {"Digital Revenue", type number}, {"Co-Brand Locations Revenue", Int64.Type}, {"Revenue 4", Int64.Type}, {"", type any}, {"Coupons & Discounts.", type any}, {"Coupons1", type number}, {"Coupons2", type number}, {"Coupons3", Int64.Type}, {"Coupons & Discounts - Co-Brand Locations", Int64.Type}, {"Other Discounts", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Gross Revenue", each [Revenue 1] + [Revenue 2] + [Revenue 3] +[Digital Revenue] + [#"Co-Brand Locations Revenue"] + [Revenue 4]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Title", "Revenue 1", "Revenue 2", "Revenue 3", "Digital Revenue", "Co-Brand Locations Revenue", "Revenue 4", "Gross Revenue", "", "Coupons & Discounts.", "Coupons1", "Coupons2", "Coupons3", "Coupons & Discounts - Co-Brand Locations", "Other Discounts"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Coupons & Discounts", each [Coupons1] + [Coupons2] + [Coupons3] + [#"Coupons & Discounts - Co-Brand Locations"] + [Other Discounts]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "% of Coupons", each [#"Coupons & Discounts"] / [Gross Revenue]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "flag and difference", each let
    K071002_Coupons = Table.SelectRows(#"Added Custom2", each [Title] = "K071002"),
    K071002_Value = K071002_Coupons[#"% of Coupons"]{0},
    CustomColumn = Table.AddColumn(#"Added Custom2", "Result", each if [Title] = "K071002" and [#"% of Coupons"] >= 0.025 then "greater than 2.5%" 
    else [#"% of Coupons"] - K071002_Value)
    in
    CustomColumn),
    Custom = #"Added Custom3"{0}[flag and difference],
    #"Demoted Headers1" = Table.DemoteHeaders(Custom),
    #"Changed Type3" = Table.TransformColumnTypes(#"Demoted Headers1",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}}),
    #"Transposed Table1" = Table.Transpose(#"Changed Type3"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers1"

And the final output is as below:

And I change some data:

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