Forum Discussion

JessieTan's avatar
JessieTan
New Member
2 years ago

Need help to convert DAX code to Power Query

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])
    )
))
Need Help

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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

  • JessieTan's avatar
    JessieTan
    New Member
    2 years ago

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