Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Replicating SQL nested queries in Power Query

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!

Need Help
  • tackytechtom's avatar
    tackytechtom
    1 year ago

    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/

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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!