Add New Column in M based on Parent

[THEG72]

I have a Table of Accounts which has an Account ID and Parent ID. I want to ADD a new Column in the Query Editor for the Parent Name.

What code should i use to add the new Parent Name field based on image below? I have the ID's  for both Parent and Acccount (Child). The Account ID will indicate what the Parent's name will be.

New Column for Parent Name based on Account ID shown in Parent ID Column

[ImkeF · Accepted solution]

You can simply use your current table as a lookuptable and use the Parent ID. Your formula would look like so:

Table.NestedJoin(#"Removed Other Columns",{"ParentAccountID"},#"Removed Other Columns",{"AccountID"},"Source",JoinKind.LeftOuter)

See this little screencast how it works: https://www.youtube.com/watch?v=looCm3cbINw

[THEG72]

@ImkeF

thanks again, the trick to change back to source is truly amazing with M....thanks for help again and the video made it easy!

Grouping Parent Headers

Here is the final code, i changed the source to my Table Select Columns so i didnt have to reselect the columnns to display

let
    Source = Accounts,
    TableSelect = Table.SelectColumns(Source,{"AccountID", "ParentAccountID", "Account Name", "AccountTypeID", "AccountLevel", "IsTotal"}),
    FitlerTable = Table.SelectRows(TableSelect, each ([AccountTypeID] = "H")),
    #"Merged Queries" = Table.NestedJoin(TableSelect,{"ParentAccountID"},FitlerTable,{"AccountID"},"FitlerTable",JoinKind.LeftOuter),
    #"Expanded FitlerTable" = Table.ExpandTableColumn(#"Merged Queries", "FitlerTable", {"Account Name"}, {"FitlerTable.Account Name"})
in
    #"Expanded FitlerTable"

Hopefully, others will find this useful to group accounts together.