Forum Discussion

Jdiepen's avatar
Jdiepen
Regular Visitor
2 years ago
Solved

Powerquery - Duplicate row values to other rows based on unique identifier

Hi ,   I have an issue in my dataset (table1) where i have a CandidateID and on one two per CandidateID i have a SessionID registered. For all the others rows with the the same CandidateID the Sess...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Hello, Jdiepen 

    let
    Source = your_table,
    g = Table.Group(
        Source, "CANDIDATE_ID", 
        {{"rows", 
        (x) => 
            [a = Table.TransformColumns(x, {"SESSION_ID", (w) => if w = "(not set)" then null else w}),
            b = Table.Sort(a, {"SESSION_ID", Order.Descending}),
            c = Table.FillDown(b, {"SESSION_ID"})][c]
        }}
    ),
    expand = Table.ExpandTableColumn(g, "rows", List.RemoveItems(Table.ColumnNames(Source), {"CANDIDATE_ID"}))
in
    expand
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

Hello, Jdiepen 

let
    Source = your_table,
    g = Table.Group(
        Source, "CANDIDATE_ID", 
        {{"rows", 
        (x) => 
            [a = Table.TransformColumns(x, {"SESSION_ID", (w) => if w = "(not set)" then null else w}),
            b = Table.Sort(a, {"SESSION_ID", Order.Descending}),
            c = Table.FillDown(b, {"SESSION_ID"})][c]
        }}
    ),
    expand = Table.ExpandTableColumn(g, "rows", List.RemoveItems(Table.ColumnNames(Source), {"CANDIDATE_ID"}))
in
    expand
Jdiepen's avatar
Jdiepen
Regular Visitor
2 years ago

Hi Ali ,

 

This is awesome, thank you!