Subqueries in Power BI

In M, the queries are typically written so that each line is basically a subquery that usually references the previous line but you can reference other lines too.

Your query might look something like this:

let
    Source = ( Your Data Source ),
    #"Filtered Year" = Table.SelectRows(Source, each ([Year] = 2015)),
    #"Calculated Average" = List.Average(#"Filtered Year"[life_expectancy]),
    #"Filtered Table" = Table.SelectRows(Source, each [life_expectancy] > 1.15 * #"Calculated Average")
in
    #"Filtered Table"

Each line references the previous one except for #"Filtered Table", which references two prior lines (Source, and #"Calculated Average").

Hi Raitup00 ,

 Based on AlexisOlson 's suggestion ,please paste the whole M to Advanced Editor dialog:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TdBLasQwDAbgu3g9CEnWw1rO0HY2pYVuugi5/zVqySljCBGJv/yRdByNsd3a/fE774yUDwSOMqsLhOYzkoa383Y0ih3XIZh4YgX1wjxGFOa0H89XsIKLZR0QadnDmPGK3nUmC5j2WQ3B87Czsqnx0hX+/H6FGwhVOM7v8jUxRagsTjvX6iWiOINlZcXpffGc/v752Fdilr24A2bloD594b7jtRKPwgMw/5sLIbumrG1/ve3RnLuY1xChtbnYka7TDETxvhrEIj/ve86wkcjAMnB0DtF/PnZ+NVlsTqQ5bpBazBnP8w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [pop_id = _t, country_code = _t, year = _t, fertility_rate = _t, life_expectancy = _t, size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pop_id", Int64.Type}, {"country_code", type text}, {"year", Int64.Type}, {"fertility_rate", type number}, {"life_expectancy", type number}, {"size", Int64.Type}}),
    #"Added Custom" =Table.AddColumn(#"Changed Type","Avg *1.15",each List.Average(Table.SelectRows(#"Changed Type" ,each [year]=2015)[life_expectancy]) *1.15   ) ,
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Avg *1.15", type number}}),
    #"Filtered Rows"=Table.SelectRows(#"Changed Type1", each [year]=2015 and [life_expectancy]> [#"Avg *1.15"])
in
    #"Filtered Rows"

Output:

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