Plan de cuentas Jerarquía padre-hijo

[Syndicate_Admin]

Hola

Estoy luchando con el Plan de Cuentas. No hay un vínculo padre-hijo entre las cuentas, es una lista simple con una columna que menciona el total de cuentas que se resumirían.

Esta es la tabla sin procesar y se ve así:

Raw tableTabla sin procesar

El resultado deseado tiene las columnas finales donde usando la columna de totalización rellena los padres en niveles separados.

Desired resultResultado deseado

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"

Espero que alguien con más experiencia pueda ayudar en esto.

Atentamente
Zosy

[Syndicate_Admin]

Aquí hay una idea general de cómo se puede lograr esto. Sin embargo, no tengo clara la estructura lógica de esa lista (no estoy seguro de si hay una)

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"

[Syndicate_Admin]

Hola
Gracias por tomarse el tiempo para mirar esto. De hecho, tampoco puedo ver la lógica en la estructura y todas las transformaciones que intenté terminaron en una relación fallida entre padres e hijos.