Get value from previous row

[Angella]

I have a data set where I want to find a value from a previous row based on the Hiearchy Level.  I'm creating AREA and FIELD columns and  want them to display the last AREA or FIELD  in the Hierarchy Entity column for all rows where Hierarchy Level is a CC.  For example, for CC 290002 and 553000, the Area column would display AB-ADMIN and Field column would display ADMIN-AB, otherwise the value should be null.  

 

[Angella]

Here's another example:

[MFelix]

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,

[Angella]

Hi Felix - Thanks for the help.  I tried pasting the formula in a custom column and it gave "Table" as a result in all the rows.

[MFelix]

Hi @Angella ,

This is not a formula has a custom column, this are all the steps you need to do, so it's the full query code.

Open the PBIX file and add each of the steps you see in it to your query. You need to recreate my steps, just paste in the code for the full query in case you wanted to copy paste the code in a blank query on your file.

[MFelix]

Hi @Angella ,

Is the information you are presenting in the order you have your data? do you want to add an addtional column?

[Angella]

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.