Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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"
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
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"
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
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!
User | Count |
---|---|
73 | |
69 | |
36 | |
26 | |
24 |
User | Count |
---|---|
97 | |
92 | |
54 | |
45 | |
41 |