Forum Discussion
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 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
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
2 Replies
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
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.
