refer value of previous row with criteria

[dading]

Hi

Can someone help me to modify table below using M query?

i have a table like this

date	Eqmt	Loc
5/23/2020 07:30	E1	A
5/23/2020 09:00	E1	B
5/23/2020 09:30	E1	null
5/23/2020 11:00	E1	null
5/23/2020 07:30	E2	C
5/23/2020 09:00	E2	null
5/23/2020 09:30	E2	D
5/23/2020 11:00	E2	null

and I want to get the latest of  loc as long as it occurs in the same eqmt, as shown below

can we do this?

date	Eqmt	Loc
5/23/2020 07:30	E1	A
5/23/2020 09:00	E1	B
5/23/2020 09:30	E1	B
5/23/2020 11:00	E1	B
5/23/2020 07:30	E2	C
5/23/2020 09:00	E2	C
5/23/2020 09:30	E2	D
5/23/2020 11:00	E2	D

thank you

[nandukrishnavs · Accepted solution]

@dading 

Use Fill option in the Edit Query.

PowerQuery for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3MtY3MjAyUDAwtzI2UNJRcjUEEo5KsTookpZWBnBJJ0xJhE40OUNDJI3o+mA2GgEJZ1w2GmHRaImk0QWXjRCNsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Eqmt = _t, Loc = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type text}, {"Eqmt", type text}, {"Loc", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Loc"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Loc"})
in
    #"Filled Down"

 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂