Extract last entry <> blank for each article

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.