Forum Discussion

THEG72's avatar
THEG72
Icon for Helper V rankHelper V
8 years ago
Solved

Add New Column in M based on Parent

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...
Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    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

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
THEG72
Icon for Helper V rankHelper V
8 years ago

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.