Replicating SQL nested queries in Power Query

[Anonymous]

hi all!

Suppose that I have a table that looks like this:

I want to add a column like below:

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!

[tackytechtom · Accepted solution]

Hi  @Anonymous ,

How about this? 🙂

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"

 

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

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!