Search code examples
powerquerym

How to insert a new line and move a value to it

There's probably an easy way to do this with PowerQuery, but I can't think of it. I want to turn this:

enter image description here

Into this:

enter image description here

...Basically just copying the first instance of each ACODE into an inserted line as a Keyword above its associated other keywords:

enter image description here

Any ideas?

Solution

  • You can group on ACODE with option All Rows, then add a row to the nested tables, remove the original ACODE column and expand the column with nested tables.

    let
    Source = Input,
    #"Grouped Rows" = Table.Group(Source, {"ACODE"}, {{"AllData", each _, type table}}),
    #"Inserted Row" = Table.TransformColumns(#"Grouped Rows",{{"AllData", each Table.InsertRows(_,0,{[ACODE = null, Keyword = _[ACODE]{0}]})}}),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Row",{"ACODE"}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Removed Columns", "AllData", {"ACODE", "Keyword"}, {"ACODE", "Keyword"})
in
    #"Expanded AllData"