Chart of Accounts Parent-Child hierarchy

[Zosy]

Hi,

I am struggling with the Chart of Accounts. There is no parent-child link between accounts, it's a simple list with a column that mentions the totaling accounts that would sum up.

This is the raw table and it looks like this:

Raw table

The desired result has the end columns where using the totaling column it populates the parents in in separate levels.

Desired result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVvbbts4EP0Vok+7QLOxRNux9823tAacxohTtEC3D4xM20Rk0ktJSbNfv0NSlmRZqi5mgD4kCnUOeWY4nBmqP358uOl0Os6Hjx/ul7OH0eP8/stogWbfl7Mvq9kKHs+5J/YUrUIS0j3lITya/TpQHlD4Cf6N6Zbxq0cREl89+PlRITqdDvwyEXs1kIRM8NZIjkJaEZ9IRoMqlCwjgt+XIggZ32bhegqOec9oSd4uh3PV7MaCR4GNyeFYtT0LAhhkAbGrLDvbH3zxRikaU043LAzQFTLPFCJiPIgk4R61QOeW0E0IJ2uClvC6ennpk0qPqMGGf7s4KtFnSvxwhx7JLwts3RK2mGRuUcZeCdXDw2ppAb5fAj9f2kC/KUH/JuTzimxs6DMooPgbfb7/upqNFgsLBMNCgvtwR6WFXdnTu3IdeWbMbcTXFkDV3ltKsaE6eBAfTekL9cVBb3LYgiKSEKXQP1Gn4/bRiu4ZJxZW4+rTYyJ4KIkXCiuIailfaPgKHnOc7yP1dpx5sKwVlS/MsxBvXUfNfAkmFZxTHz1QT0ZMjQGxgtACwVD5kT7RUMPDcMbXyVGoz9O//jJ4GrlnjuxP4JgSsAlfo9Ea7MkCMELIXir3WMlx2+vo4/bEiza0WupyODXNkeeJiGtdb2uAnbDHL+SlBWTXeF0Q+baR1bGyoNt0jA1QdXrcwSm41QhaVdiU47t79GUMZ8cLDUL9B3LtWWJUh8gd3T+Bf+/YIdBeMo2sLSh17TbucuLfyu3AvzVoDK8TqwVsc3jjMhd0nQySWs4xojzA7nklfiVy9t1CKdxUioYzzqngGhXcRAWdEMI8FXT8YnshsI52EuI+THBBSVAZJU6Zi0IcoKp9eL/ZwMLRHWE8pFwlQfUiaC0CnBJ8DZkPARpsaA8+NV48nNYXOmc/bOyHE/t1dXlA+DOa7Ijcpr43B50kbPnWxtSJ/SXIv3u3WCmd3CdD4MibkAPTTl/Hl1rw4RyfCpYLUV05tKBKneDkZRUyG5gq5w5d4w7dxB16ndSVFTTsRI/6UAtREbXf1z2Tf+0PEUzUGKMSLDOJu9wkztUxmeVOhMITB/ZOHLqW+zdiJl39vBpP3oWmq7WKpFpGbGW7BL34MRz0doFV0XYL4a9mrtMM/CZ943jOQHaikOFUp/ItqRqiw8Gv0Yhpxj4oZNfVll2iYSHRUjIej7TH1e+YTcPp9S35da2jCKfhUUi9OFUU7CNV1dSpCprRq6DwlT9z8cqTEw3WKqlH2SFE8GBte8k3aRS9IMjlgmjPBNGbJIgOTJNMHgSUOhQ9SgJVbuvgOXAK0EAn+IEH8DCsZZozgEJ9Bm4xl6qP9mJtkwkXM91RyHePLjhTLhlCyhaT2ODtFvPWbZzU4ki9LMFP3mzuXwPjX4PEv4YdPaiBNqXONXTyUEoLvTWqME/fKhQi01w4G91QhKERIWkvmP597Y5mmQCO7lLUhskOPFuu00mX22Ripys1jRQNFsPqhscUStewTse9dKW60VEbJhlYaFknU1s3mVluqaamdpKa2lxWzB9RmmY1KHWKl+2e5J5BnB0cI0wgNuErkdWGz84pOZgLpXH1DUMyOO4FHgkzBahNThVNj+3IOd9IEoQy8sJI0trNyCZ0GUdvaaycJ5i+gpP0FcxFk+1CxNENhk9HW4yOpyg4ARoL8VyF2yTXcPA75BqOqd+dpH53MiH2ol5rLtx2HM2ThFvTQr8j8pnqXqbiWBG//b40HfTR+gVOBRYoyNgoSwmZxkWRzjTNxxGg0iDI3jFcp+d3QlPFUjXFM8O7uu+r/cqvlTC1YMC51+IK4R2YumdMI78yl23B0ztxr4b7sgVf/2xdU7bVvaJkFu/AqorY9/S8wYmKdSN/C6LhmXxx9LHP5Z5lu+pWIt7d9ukyAfV3LzcOqRDwVEhNL8iwzmDBz9cUBTvxmuQknuAbKimvYbmyEIh1uDbYK4VdBZQZepzGJJ1GYRaAO/H9Vt3JtuI4KaUuU+rUHtjk2TjJs7ET20MVbDEBUXVv3cK31BiOkwI3rdtP5zPKzqdYsePp07xwb06Fs1TtK/fmxN0scb3SvTnJWRXf0ilyfmeKHpwUPVgHuCkN2JaDR9e4NCp1NF3rxEj6+klFSsbXUPgcu6M1650MSLE6usrJUWXj5SdJDjvmVdeZlUS4YE1SeDuobYLr2ZqFQrIad6TVRLozNEXxwFiwaQT1oul+XinxPsLf98ICW+98Wd/oU8CqO0TV2JnivJlb5VzVVGU4qcowNreFa7SmTzBlmmmMtPFX7MRwUwVXBZMMROmz88Xj7EVZPNHmKzd1Fk7qLKzvSdPUaiKghgtVwtN+t+oL0imT1AtVW8Cvd2NcNodzJbrmcytzc6ACNOFRJkQvifdMtjXy3AaMaq+uci2W9CWLRKmR05e8RjbJGdzchOLkJhQPiyialL359E9X1PiY/vU7Ov0b7SERYP+ZY0hs0EGKA8TQN81Ej42Vli7WN99KZTmqkHJj88r3sx/VEFtzP1FKCQNK9ZMvbfrmS5u8UnMeEr5lTz5FoyCgrU/Nvvn85gT+Kjknj5/LXKybW65bm5XkJHONZG4iGS6SLPu1Za1YUyoZLpBsSiEXl3TdnKZSvGG5eG3WlBMPG/GSnRn31O4fRwtU9F8O0B/w9PufTYk6pqmm4X/+/B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Name = _t, Income_Balance = _t, Account_Category = _t, Account_Subcategory_Descript = _t, Account_Type = _t, Totaling = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Name", type text}, {"Income_Balance", type text}, {"Account_Category", type text}, {"Account_Subcategory_Descript", type text}, {"Account_Type", type text}, {"Totaling", type text}})
in
    #"Changed Type"

Hope someone with more experience can help on this.

Kind regards,
Zosy
 

[lbendlin]

Here is a general idea of how this can be achieved. However I am not clear on the logical structure of that list (not sure if there even is one)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVvbbts4EP0Vok+7QLOxRNux9823tAacxohTtEC3D4xM20Rk0ktJSbNfv0NSlmRZqi5mgD4kCnUOeWY4nBmqP358uOl0Os6Hjx/ul7OH0eP8/stogWbfl7Mvq9kKHs+5J/YUrUIS0j3lITya/TpQHlD4Cf6N6Zbxq0cREl89+PlRITqdDvwyEXs1kIRM8NZIjkJaEZ9IRoMqlCwjgt+XIggZ32bhegqOec9oSd4uh3PV7MaCR4GNyeFYtT0LAhhkAbGrLDvbH3zxRikaU043LAzQFTLPFCJiPIgk4R61QOeW0E0IJ2uClvC6ennpk0qPqMGGf7s4KtFnSvxwhx7JLwts3RK2mGRuUcZeCdXDw2ppAb5fAj9f2kC/KUH/JuTzimxs6DMooPgbfb7/upqNFgsLBMNCgvtwR6WFXdnTu3IdeWbMbcTXFkDV3ltKsaE6eBAfTekL9cVBb3LYgiKSEKXQP1Gn4/bRiu4ZJxZW4+rTYyJ4KIkXCiuIailfaPgKHnOc7yP1dpx5sKwVlS/MsxBvXUfNfAkmFZxTHz1QT0ZMjQGxgtACwVD5kT7RUMPDcMbXyVGoz9O//jJ4GrlnjuxP4JgSsAlfo9Ea7MkCMELIXir3WMlx2+vo4/bEiza0WupyODXNkeeJiGtdb2uAnbDHL+SlBWTXeF0Q+baR1bGyoNt0jA1QdXrcwSm41QhaVdiU47t79GUMZ8cLDUL9B3LtWWJUh8gd3T+Bf+/YIdBeMo2sLSh17TbucuLfyu3AvzVoDK8TqwVsc3jjMhd0nQySWs4xojzA7nklfiVy9t1CKdxUioYzzqngGhXcRAWdEMI8FXT8YnshsI52EuI+THBBSVAZJU6Zi0IcoKp9eL/ZwMLRHWE8pFwlQfUiaC0CnBJ8DZkPARpsaA8+NV48nNYXOmc/bOyHE/t1dXlA+DOa7Ijcpr43B50kbPnWxtSJ/SXIv3u3WCmd3CdD4MibkAPTTl/Hl1rw4RyfCpYLUV05tKBKneDkZRUyG5gq5w5d4w7dxB16ndSVFTTsRI/6UAtREbXf1z2Tf+0PEUzUGKMSLDOJu9wkztUxmeVOhMITB/ZOHLqW+zdiJl39vBpP3oWmq7WKpFpGbGW7BL34MRz0doFV0XYL4a9mrtMM/CZ943jOQHaikOFUp/ItqRqiw8Gv0Yhpxj4oZNfVll2iYSHRUjIej7TH1e+YTcPp9S35da2jCKfhUUi9OFUU7CNV1dSpCprRq6DwlT9z8cqTEw3WKqlH2SFE8GBte8k3aRS9IMjlgmjPBNGbJIgOTJNMHgSUOhQ9SgJVbuvgOXAK0EAn+IEH8DCsZZozgEJ9Bm4xl6qP9mJtkwkXM91RyHePLjhTLhlCyhaT2ODtFvPWbZzU4ki9LMFP3mzuXwPjX4PEv4YdPaiBNqXONXTyUEoLvTWqME/fKhQi01w4G91QhKERIWkvmP597Y5mmQCO7lLUhskOPFuu00mX22Ripys1jRQNFsPqhscUStewTse9dKW60VEbJhlYaFknU1s3mVluqaamdpKa2lxWzB9RmmY1KHWKl+2e5J5BnB0cI0wgNuErkdWGz84pOZgLpXH1DUMyOO4FHgkzBahNThVNj+3IOd9IEoQy8sJI0trNyCZ0GUdvaaycJ5i+gpP0FcxFk+1CxNENhk9HW4yOpyg4ARoL8VyF2yTXcPA75BqOqd+dpH53MiH2ol5rLtx2HM2ThFvTQr8j8pnqXqbiWBG//b40HfTR+gVOBRYoyNgoSwmZxkWRzjTNxxGg0iDI3jFcp+d3QlPFUjXFM8O7uu+r/cqvlTC1YMC51+IK4R2YumdMI78yl23B0ztxr4b7sgVf/2xdU7bVvaJkFu/AqorY9/S8wYmKdSN/C6LhmXxx9LHP5Z5lu+pWIt7d9ukyAfV3LzcOqRDwVEhNL8iwzmDBz9cUBTvxmuQknuAbKimvYbmyEIh1uDbYK4VdBZQZepzGJJ1GYRaAO/H9Vt3JtuI4KaUuU+rUHtjk2TjJs7ET20MVbDEBUXVv3cK31BiOkwI3rdtP5zPKzqdYsePp07xwb06Fs1TtK/fmxN0scb3SvTnJWRXf0ilyfmeKHpwUPVgHuCkN2JaDR9e4NCp1NF3rxEj6+klFSsbXUPgcu6M1650MSLE6usrJUWXj5SdJDjvmVdeZlUS4YE1SeDuobYLr2ZqFQrIad6TVRLozNEXxwFiwaQT1oul+XinxPsLf98ICW+98Wd/oU8CqO0TV2JnivJlb5VzVVGU4qcowNreFa7SmTzBlmmmMtPFX7MRwUwVXBZMMROmz88Xj7EVZPNHmKzd1Fk7qLKzvSdPUaiKghgtVwtN+t+oL0imT1AtVW8Cvd2NcNodzJbrmcytzc6ACNOFRJkQvifdMtjXy3AaMaq+uci2W9CWLRKmR05e8RjbJGdzchOLkJhQPiyialL359E9X1PiY/vU7Ov0b7SERYP+ZY0hs0EGKA8TQN81Ej42Vli7WN99KZTmqkHJj88r3sx/VEFtzP1FKCQNK9ZMvbfrmS5u8UnMeEr5lTz5FoyCgrU/Nvvn85gT+Kjknj5/LXKybW65bm5XkJHONZG4iGS6SLPu1Za1YUyoZLpBsSiEXl3TdnKZSvGG5eG3WlBMPG/GSnRn31O4fRwtU9F8O0B/w9PufTYk6pqmm4X/+/B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Name = _t, Income_Balance = _t, Account_Category = _t, Account_Subcategory_Descript = _t, Account_Type = _t, Totaling = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Name", type text}, {"Income_Balance", type text}, {"Account_Category", type text}, {"Account_Subcategory_Descript", type text}, {"Account_Type", type text}, {"Totaling", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Category 1", each if [Account_Type]="Begin-Total" and #"Added Index"{[Index]+1}[Account_Type]="Begin-Total" then [Name] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category 2", each if [Account_Type]="End-Total" then [Name] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Category 1"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Category 2"})
in
    #"Filled Up"

[Zosy]

Hi,
Thank you for taking the time to look at this. Indeed I can't see the logic in the structure either and all the transformations I tried ended up in a failed parent-child relationship.