Bringing previous record in power query

[ahmedismailfourtex]

Hello,
I am working with a table like this:

M/C​	Date​	Record​	Needed Column​
1​	17/01/2020​	x​	y​
1​	15/01/2020​	y​	y​
1​	14/01/2020​	y​	x​
1​	10/01/2020​	x​

and I need to bring the previous record for the same M/C in a (Needed Column), in power query.
Thanks in advance .

 

[sandy666]

at first look...

M/C	Date	Record	Record.1
1	17/01/2020	x	y
1	15/01/2020	y	y
1	14/01/2020	y	x
1	10/01/2020	x

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Index = Table.AddIndexColumn(Type, "Index", 0, 1),
    Index1 = Table.AddIndexColumn(Index, "Index.1", 1, 1),
    Join = Table.NestedJoin(Index1,{"Index.1"},Index,{"Index"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Record"}, {"Record.1"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort,{"Index", "Index.1"})
in
    RC

 

[ahmedismailfourtex]

at first look...

M/C	Date	Record	Record.1
1	17/01/2020	x	y
1	15/01/2020	y	y
1	14/01/2020	y	x
1	10/01/2020	x

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Index = Table.AddIndexColumn(Type, "Index", 0, 1),
    Index1 = Table.AddIndexColumn(Index, "Index.1", 1, 1),
    Join = Table.NestedJoin(Index1,{"Index.1"},Index,{"Index"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"Record"}, {"Record.1"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort,{"Index", "Index.1"})
in
    RC

Thank you for your reply, but it doesn't work for different values in column "M/C"
if we added M/C no 2 it will come to M/C no 1.

 

[sandy666]

I need to bring the previous record for the same M/C

 

[ahmedismailfourtex]

I got it by adding one more column for M/C in "merge" as a match.

 

[sandy666]

that's what I did with Join
thanks
have a nice day

You can change the solution post by clicking on another one that helped you better.
And you can always Like the posts that you like or helps you​