Forum Discussion
Post ProdigyExtract last entry <> blank for each article
Hi community!
I have a archive for our articles. Unfortuntely, there are some missing records that I would like to extract using PQ.
The table is structured as the following:
| Year-Week | Article | Attribute 1 | Attribute 2 |
| 2024-01 | A | 1 | 2 |
| 2024-01 | B | 2 | 2 |
| 2024-02 | A | 2 | |
| 2024-03 | A | ||
| 2024-03 | B | 2 | 2 |
As you can see the Atrribute 1 and 2 are blanks since week 02. Now I would like to create a query that shows me for all articles in the second column the last available entry for Attribute 1 and 2.
How would you do that?
hi, use this for attribute 1 and apply the same for attribute 2
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(X, "Custom", each List.Last(List.RemoveNulls(Table.SelectRows(Table.FirstN(X,[Index]),(y)=>y[Article]=[Article])[Attribute 1]))) in #"Added Custom"Hi,
Thanks for the solutions Omid_Motamedise and uzuntasgokberk offered, and i want to offer some more information for user to refet to.
hello joshua1990 , you can create a blank query and input the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MFTSUXIEYhBtpBSrgyzuBBZDFTeCqldAEzdGEldAE0cyJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year-Week" = _t, Article = _t, #"Attribute 1" = _t, #"Attribute 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year-Week", type date}, {"Article", type text}, {"Attribute 1", Int64.Type}, {"Attribute 2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Attribute_1_Value", each let a=[Article], b=[#"Year-Week"] in List.Max(Table.SelectRows(#"Changed Type",each [Article]=a and [#"Year-Week"]<=b and [Attribute 1]<>null)[Attribute 1])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Attribute_2_Value", each let a=[Article], b=[#"Year-Week"] in List.Max(Table.SelectRows(#"Changed Type",each [Article]=a and [#"Year-Week"]<=b and [Attribute 2]<>null)[Attribute 2])) in #"Added Custom1"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- uzuntasgokberk
Super User
Hello joshua1990 ,
I assume that you would like to do PQ(Power Query).
In Add column section you need to create custom column. İf you would not like to see both null.You can write a code like the below image. İf you would like to see one of them not null. Make the code instead of and to or.
After that filter it.
Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok | - Omid_Motamedise
hi, use this for attribute 1 and apply the same for attribute 2
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(X, "Custom", each List.Last(List.RemoveNulls(Table.SelectRows(Table.FirstN(X,[Index]),(y)=>y[Article]=[Article])[Attribute 1]))) in #"Added Custom" Hi,
Thanks for the solutions Omid_Motamedise and uzuntasgokberk offered, and i want to offer some more information for user to refet to.
hello joshua1990 , you can create a blank query and input the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MFTSUXIEYhBtpBSrgyzuBBZDFTeCqldAEzdGEldAE0cyJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year-Week" = _t, Article = _t, #"Attribute 1" = _t, #"Attribute 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year-Week", type date}, {"Article", type text}, {"Attribute 1", Int64.Type}, {"Attribute 2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Attribute_1_Value", each let a=[Article], b=[#"Year-Week"] in List.Max(Table.SelectRows(#"Changed Type",each [Article]=a and [#"Year-Week"]<=b and [Attribute 1]<>null)[Attribute 1])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Attribute_2_Value", each let a=[Article], b=[#"Year-Week"] in List.Max(Table.SelectRows(#"Changed Type",each [Article]=a and [#"Year-Week"]<=b and [Attribute 2]<>null)[Attribute 2])) in #"Added Custom1"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
