Subqueries in Power BI

[Raitup00]

Hi everyone

I have the following query in SQL:

SELECT * FROM population
WHERE life_expectancy > 1.15 *

(SELECT AVG(life_expectancy)
FROM population
WHERE year = 2015)
AND year = 2015;

It is a subquery and I'm trying to get the same result but in Power query.

This is an extract of the table

How can do that in Power query?

Thanks in advance

Ray

[AlexisOlson · Accepted solution]

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").

[v-eqin-msft · Accepted solution]

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.

[AlexisOlson · Accepted solution]

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").

[Greg_Deckler]

@Raitup00 Just create a step in Advanced Editor where you calculate the average of life expectancy in 2015 and multiply by 1.15. Then you can create a column that figures out if the life expectancy is greater than that and filter to those rows.

[Raitup00]

@Greg_Deckler 

Thank for your prompt reply. 

I'm not an expert in the Advanced editor. I added a calculated column using List.Average but the first argument is indeed a list, the values in the column "life_expectancy" is not a list. 

Could you give a hint or idea how to insert a step using Advanced editor?