Need help to convert DAX code to Power Query

[JessieTan]

I had a DAX formula as below but now I need to use Power query. Can someone help me to convert it? thanks

**bleep** Run Plan =

CALCULATE (

    SUM ( [Run QTY] ),

    FILTER (

        ALLEXCEPT ( 'Run 4wk','Run 4wk'[LW],'Run 4wk'[TYPE] ),

        'Run 4wk'[DATE1] <= EARLIER( ( 'Run 4wk'[DATE1])

    )

))

[JessieTan]

Thanks for you help. However, the query are too complicated for me. I will try other way. Thank you. 

[OwenAuger]

@JessieTan 

Here's a sample query showing one way to do it with dummy data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8glXMDBU0lEKiQxwhbCMDIyMdQ0MdcEcQ6VYHdyKjCCKjLAoMkI3yQSvIqhJFnBFRnjcZIZXFdQoYyMsqjBcZWaCVxXULEMjoMNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LW = _t, TYPE = _t, DATE1 = _t, #"Run QTY" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LW", type text}, {"TYPE", type text}, {"DATE1", type date}, {"Run QTY", type number}}),
    #"Self Merge on LW & TYPE" = Table.NestedJoin(#"Changed Type", {"LW", "TYPE"}, #"Changed Type", {"LW", "TYPE"}, "Self Merge", JoinKind.LeftOuter),
    #"Filter Self Merge Dates" = Table.ReplaceValue(#"Self Merge on LW & TYPE", each [Self Merge], each let CurrentDate = [DATE1] in Table.SelectRows([Self Merge], each [DATE1] <= CurrentDate ), Replacer.ReplaceValue, {"Self Merge"}),
    #"Aggregate Self Merge" = Table.AggregateTableColumn(#"Filter Self Merge Dates", "Self Merge", {{"Run QTY", List.Sum, "**bleep** Run Plan", type number}})
in
    #"Aggregate Self Merge"

The query joins the table with itself on LW & TYPE, then filters the joined table, and sums the Run QTY.

Here's a variation that uses Table.AddColumn rather than using Table.ReplaceValue when filtering the joined table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8glXMDBU0lEKiQxwhbCMDIyMdQ0MdcEcQ6VYHdyKjCCKjLAoMkI3yQSvIqhJFnBFRnjcZIZXFdQoYyMsqjBcZWaCVxXULEMjoMNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LW = _t, TYPE = _t, DATE1 = _t, #"Run QTY" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LW", type text}, {"TYPE", type text}, {"DATE1", type date}, {"Run QTY", type number}}),
    #"Self Merge on LW & TYPE" = Table.NestedJoin(#"Changed Type", {"LW", "TYPE"}, #"Changed Type", {"LW", "TYPE"}, "Self Merge", JoinKind.LeftOuter),
    #"Added Self Merge Filtered" = Table.AddColumn(#"Self Merge on LW & TYPE", "Self Merge Filtered", each let CurrentDate = [DATE1] in Table.SelectRows([Self Merge], each [DATE1] <= CurrentDate )),
    #"Remove Self Merge" = Table.RemoveColumns(#"Added Self Merge Filtered",{"Self Merge"}),
    #"Aggregate Self Merge Filtered" = Table.AggregateTableColumn(#"Remove Self Merge", "Self Merge Filtered", {{"Run QTY", List.Sum, "**bleep** Run Plan", type number}})
in
    #"Aggregate Self Merge Filtered"

Does something like this work at your end?

Regards