Forum Discussion

hworrall's avatar
hworrall
Frequent Visitor
7 years ago

Complex query

Hi there,   I am new to Power BI and am trying to write a query where I need to subtract the result of one query from the result of a second query.  Here is what I have in SQL/English.  There are a...
Need Help
Tips and Tricks
Tutorial Requests
ImkeF's avatar
ImkeF
Community Champion
7 years ago

Hi hworrall ,

please paste this code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0MLJR0wxxzGKQdiQwOlWB18ijKA2IiQokQgNkZTZInNOhNirDMlxjozNEXmUEVwDsg6c6CiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Wdate = _t, Wipdate = _t, Windicator = _t, TotalCharge = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wdate", type date}, {"Wipdate", type date}, {"Windicator", type text}, {"TotalCharge", Int64.Type}}),
    Filter1 = Table.SelectRows(#"Changed Type", each [Wdate] < #date(2019, 5, 1) and [Wipdate] > #date(2018, 4, 30) and ( [Windicator] = "w" or [Windicator] = "h" ) ),
    Query1 = List.Sum(Filter1[TotalCharge]),
    Filter2 = Table.SelectRows(#"Changed Type", each [Wdate] > #date(2018, 4, 30) and [Wipdate] < #date(2019, 5, 1) and ( [Windicator] = "w" or [Windicator] = "h" ) ),
    Query2 = List.Sum(Filter2[TotalCharge]),
    Result = Query1 + Query2
in
    Result