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 ...
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/

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost 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!