Power Query change an existing Chart of Accounts into another format

[Ackbar-Learner]

Hi I have an existing Chart of Accounts which is in a report format and i need it same by columns for further drill down analysis.

I am attaching below the link to the initial format and the needed format in 2 different sheets. I need to use power query to transform it to the new format.

Book1.xlsx

Thanks

[v-cgao-msft]

Hi @Ackbar-Learner ,

I'm stuck on the Fill down logic, hope this helps, here's the code in my advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVdNb+M2EP0rg9wWSI3IySbeo+NsAQOLNnCD7GG7B0oa2WwkUiUpO/n3nSElR7ZDS10eElvmfPDNm8fRjx8X0K75BP5qUpsZmWIOmailEyU0qhYyv7i8mM/pz5PmZ3Nr0dmLn5fvxvcT+F2bSjipFeBrjcqiZav7c1YLspKvFE2E32j/4tx+XssJLJUTai3TEvuGyyFLXtMJLLTK0FpK1F5CLRwqRx9KmSE/vwRnRI5QCfNiQagcrKxkKQwYud648ChEBVmA2+Ab7NBgSGE6Jgde4hOI7N9GGjp8oQ1sRdkIPlBGackcTUCSgymkPUo7SBHsRu8U1YQ2wAKWv123YcUgasslY62EyiTt6sO2HAVcQuzYCIN0akqrKGQpCbo85OLEi1Trvb/knMMHqkBjDKHe/UpWD8P5T+ABU6dN2D866SdfzfzA9Gx+R1VKMdMVHQ7yBn05avHmCyUKR0UoiUlEAqFAK4Q3FKaLMViSlo7zSjfEQNA7QjN9O4ctuR1NsHQw9Uob/DD1aTomyM0E/iT2myNsbwYr+UwEEHYDghgt1IvPjDhFieTBx/M5F18n8GiQTlJx34ZuzDLTIDuh8/pG/HrOw5zjB3Fja6I0mq0Xq8d3ifsmRUplcBI/VKBTofTmy3H2y7aXoDZYyabiEzAJgo9xTp4ZB11IB6QepSYWpkY3pFAsJzthcu/seZwvSmiFtim9MWsaFHupaIlBvoYTu/dJbaUXVza6HzShK2BBIihbCj0uRmXcEjA0d3ZgfzPaQbd+scsplBgda9Z1y2Gys19J9km8gmjcRpuw3Tsan0q3sk8fp5T9b0+85BkQd9Jt5BF2s0wOxnlgzS/QmH5zPz4M2pHs/4EOXGOU3iLHW7HkP/7dXF1Nb78d7CUFfhZGhuuWkrROZ3Tx64JbQNoNhV5rndtOpnbavPD/2ug1SQdjtprGnF9P4Dvvr9FQY1VB4Lm9esruW076C4C8NqbWNDn5aK2ySIvczKvrWJS9Euvazw3kc4/W6iZm9Zl6XuxoziGCU5+3Ialvm4qLFr5r7xdfaY8iwHuD3epzzHHL0rPOvQNxzkPasXMfPCPJXLemacz0lqTV0RVKwawfLVa3sa13XPmSqZr/01jn7xM2uBs4GBWfCl9j5jxLTqbeAByNhvsZ1TNn/7U4nHhXd1EcZqeF7VdgFrP7wiMyEwAKoytqQ+NkJuuOGoQntuB8iblIrg59BCJIRRele795Sy2U9RCwZ47jD745PWRyFY2UdKfsMiN4M5Rb0WHXDeDvpE6i7dzWiLpLbnm05tzjExU5GiKhPkwtlk+UkMm0u+wJmfrwxqYn/YS6MaB9JQhTPoOJ9KLg3qBC0nwvBUlUcZLrD1h9TNmj14COsP3qbrDk8kHWjuq9SkZ1KLnxr2YBqP0F2gOs18FJVJeGcT9wEweeBI5vSq2OYPd2UfVKbk8GqzADOPHq+9ybR0UluevYTPtJC/i9rf/SJh1WFhJwGryTVRKVm2R2kkl0Olslh2Lw8z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Headers = _t, Codes = _t, #"FS-Section" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Headers", type text}, {"Codes", type text}, {"FS-Section", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Level 1", each if List.Contains({"A".."H"}&{"0".."9"},Text.Start(Text.Trim([Headers]),1)) and Text.Start([Headers],9)<>" " then Text.Combine(List.Range(Text.Split(Text.Trim([Headers])," "),1)," ") else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Level 2", each if List.Contains({"I".."X"},Text.Start(Text.Trim([Headers]),1)) then Text.Combine(List.Range(Text.Split(Text.Trim([Headers])," "),1)," ") else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Level 3", each if List.Contains({"0".."9"},Text.Start(Text.Trim([Headers]),1)) then Text.Combine(List.Range(Text.Split(Text.Trim([Headers])," "),1)," ") else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Level 4", each if List.Contains({"a".."z"},Text.Start(Text.Trim([Headers]),1)) then Text.Combine(List.Range(Text.Split(Text.Trim([Headers])," "),1)," ") else null),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom3", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Ackbar-Learner]

Hi thanks for your reply.

Ignoring the fill down for the moment, even then there are some rows which do not match. I have higlighted them in yellow in the above link on a new sheet called Table2. I am very new to the codes that you have written 🙂 so it will take me some time to digest.

I don't know if this can help but the Codes column does explain the parent-child relationship except that there is no delimiter in the code to be able to distinguish where it starts and ends.

The first Letter A represents the FS-Section. After this, the second character or second group of characters will match the first character or first group of characters in the Headers column to determine the hierarchy level. The main issue are the roman numerals which are a group of characters like III, IV, etc..

Thanks for your time on this.

Regards