Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Replicating SQL nested queries in Power Query

hi all!

 

Suppose that I have a table that looks like this:

pmajlessi_0-1733336145353.png

I want to add a column like below:

pmajlessi_1-1733336181246.png

The new column, "info" literally looks into the column "information" and if it couldn't find a value there, it looks at the last row with a value and copies it in.

I can solve this in MSSQL by the below code:

select 
[id]
,[info] = case 
	when [information] = '' 
		then (select [information] from dbo.table where [id] = (select max([id]) from dbo.table where [id] < src.[id] and [information] <> ''))
	else [information]
end
from dbo.table as src

How can i replicate that in Power Query's M Language?

I tried List.Select but it gave me errors.

 

your help is much appreciated!

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi  @Anonymous ,

How about this? 🙂

tackytechtom_6-1733343869215.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaqorFKK1YlWMgKyE5OSwWxjIBvMMIExTGEMMyAjIy8bzDaHCVrAGJYwA2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, information = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"information", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "info", each if [information] = "" then null else [information]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"info"})
in
    #"Filled Down"


Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

2 REPLIES 2
tackytechtom
Super User
Super User

Hi  @Anonymous ,

How about this? 🙂

tackytechtom_6-1733343869215.png

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaqorFKK1YlWMgKyE5OSwWxjIBvMMIExTGEMMyAjIy8bzDaHCVrAGJYwA2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, information = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"information", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "info", each if [information] = "" then null else [information]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"info"})
in
    #"Filled Down"


Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Anonymous
Not applicable

Sir, you are a genius!
this was so simple, yet when I tried "Column from example" feature, it couldn't offer me a solution.

brilliant! who would have thought?

the entire day i was searching for solutions using "List.Select", "List.PostionOf" and "List.Generate" and their related errors, and all this time it was a built in feature, that only if I searched for Table.FillDown I could have found it much earlier, and saved myself a great deal of headache!

I love this forum!

and I love Power BI!

and Sir, thank you very much, you are a life saver!

Wish you all the best!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.