Forum Discussion
Subqueries in Power BI
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
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.
4 Replies
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").
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.
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?
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.
