Forum Discussion

Thomas_DE's avatar
Thomas_DE
Regular Visitor
4 years ago
Solved

Referencing data from another row

I have a Table with the following structure Title Entry Data A 10 MasterData 1 A 23 SpecialData A 54 null B 10 MasterData 2 B 34 null B 576 null C 10 null ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    NewStep=Table.Combine(Table.Group(PreviousStepName,"Title",{"n",each Table.ReplaceValue(_,"","",(x,y,z)=>x??_{[Entry=10]}?[Data],{"Data"})})[n])

jbwtp's avatar
jbwtp
Memorable Member
4 years ago

Hi Thomas_DE,

 

I think this does what you are after. Please try.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABK+icUlqUUuiSWJCoZKsToQGSNjIBFckJqcmZgDkoJLmJoAibzSnBywiBMWQ4zgMsYYak3NzZCFnGHaUUSMDI0NjeCCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Entry = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Entry", Int64.Type}, {"Data", type text}}),
    fTransform = (t as table) as table =>
        let 
            Transform = List.Skip(List.Accumulate(Table.ToRecords(t), {{null, {}}}, (a, n)=>  a &  (if n[Entry] = 10 then {{n[Data], n }} else {{List.Last(a){0}, if n[Data]="null" then Record.TransformFields(n, {{"Data", (x)=> List.Last(a){0}}}) else n }}))),
            Output = Table.FromRecords(List.Zip(Transform){1}, Value.Type(t))
        in
            Output,
    next = #"Changed Type",
    #"Grouped Rows" = Table.Combine(Table.Group(next, {"Title"}, {{"Count", fTransform}})[Count])
    in #"Grouped Rows"

 

 

Kind regards,

John