Forum Discussion
Get value from previous row
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...
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,
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.
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.