Get value from previous row

I want to add two additional columns - AREA and FIELD which would populate for all CC entities.  My problem is that the associated hierarchy is listed above each CC in the same column when I need it in separate columns.  Hopefully that's clear.  I only need CC's in my dataset, but I need the associated hierarchy.  

Hi Angella ,

You need to add some additional logic and use some replace and fill down but check if this code works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BCsMgFETv4rYRJiaWuDTGltA0H7Q2i5D7X6MqBdHle8zM/+fJDO2eNtYxRy92dVE8QyQ9Z9DO6kxcL+91z+6x2m1JMhn+DxoTjVCAKCjlAKDZMbYamV0wlodSGgTQo8oc1n8ceVtCSmBUU8PqVreIFvd19XcShcd0qS88Rca9HgUajvnrBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Hierarchy Level" = _t, #"Hierarchy Entity" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "AREA", each if [Hierarchy Level] = "AREA" then [Hierarchy Entity] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "FIELD", each if [Hierarchy Level] = "FIELD" then [Hierarchy Entity] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1", {"AREA"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",each [AREA], each if [Hierarchy Level] = "CC" then [AREA] else "",Replacer.ReplaceText,{"AREA"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value",{"FIELD"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Filled Down1",each [FIELD], each if [Hierarchy Level] = "CC" then [FIELD] else "",Replacer.ReplaceText,{"FIELD"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","",null,Replacer.ReplaceValue,{"AREA", "FIELD"})
in
    #"Replaced Value2"

PBIX attach,