The Definitive Guide for Chart of Accounts in PBI

In this post, we are checking the method that I created to generate a proper dimension for BI base in a chart of accounts from a company.

Video Explanation

tl;dv

To create this function your chart of accounts will need to have this columns:

• Classification, Ex: “1.01.02.003.0576”
• Description, Ex: “Income Taxes to compensate”
• Any other columns that you may need like account code.

Furthermore, it is important that:

• The chart does not need to be regular with this method.
• The classification column needs to use “.” as a separator
• Synthetic Accounts can not have the full path, this means:
  • This example will work:
    • 1 – Assets
    • 1.1 – Current
    • 1.1.01 – Cash and Equivalents
    • 1.1.01.001 – Petty Cash
  • But this will not:
    • 1.0.00.000 – Assets
    • 1.1.00.000 – Current
    • 1.1.01.000 – Cash and Equivalents
    • 1.1.01.001 – Petty Cash

To use this method you will need to add this custom function, which is explained in detail in the video. And invoke it by passing the table containing the ChartOfAccounts, the name of the Classification Column, and the name of the Account Name.

let
    Fonte = (TableChartOfAccounts as table, ColumnClass as text, ColumnAccountName as text) => let
    //Code from biex.com.br
    Fonte = TableChartOfAccounts,
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{ColumnClass, type text}, {ColumnAccountName, type text}}),
    //Essa coluna será importante no fim do processo para filtrar as linhas que não são analíticas. Ela deriva da coluna Classificação.
    TipoDeConta = Table.AddColumn(#"Tipo Alterado", "Tipo", each (
    let
        Cla = Record.Field(_, ColumnClass),
        Qtd = Table.RowCount(Table.SelectRows(#"Tipo Alterado", each Text.StartsWith(Record.Field(_, ColumnClass), Cla))),
        Log = if Qtd > 1 then "S" else "A"
    in
        Log
    )),
    //Essa coluna serve para indicar o nível do plano de contas, ela é derivada da Classificação contando a quantidade de pontos na coluna
    NivelDaConta = Table.AddColumn(TipoDeConta, "Nivel", each Text.Length(Record.Field(_, ColumnClass)) - Text.Length(Text.Replace(Record.Field(_, ColumnClass),".",""))+1),
    //Etapa importante para que os grupos sejam criados corretamente.
    Ordenar = Table.Sort(NivelDaConta,{{ColumnClass, Order.Ascending}}),
    //Essa será a coluna presente em todos os grupos, ela é a Classificação seguida da descrição da conta.
    #"Classificação+Descrição" = Table.AddColumn(Ordenar, "Grupo Analitico", each Record.Field(_, ColumnClass) & " - " & Record.Field(_, ColumnAccountName)),
    //Para saber quantos grupos serão criados
    MaiorGrupo = List.Max(Ordenar[Nivel]),
    //A lista com o nome dos grupos, para criar os registros que serão expandidos
    ListaNome = List.Generate(
    () => [x = 1, Nome = "Grupo1"],
    each [x] <= MaiorGrupo,
    each [x = [x] + 1, Nome = "Grupo" & Text.From([x]+1)],
    each [Nome]),
    //A função mais importante que Cria todos os grupos nescessários
    CriaGrupos = Table.AddColumn(#"Classificação+Descrição", "Grupos", each 
    let
        GrupoAnalitico = [Grupo Analitico],
        Nivel = [Nivel],
        Lista = List.Generate(
            () => [Valor = if Nivel = 1 then GrupoAnalitico else if Nivel < 1 then "@" else null, x = 1],
            each [x] <= MaiorGrupo,
            each [Valor = if Nivel = [x]+1 then GrupoAnalitico else if Nivel < [x]+1 then "@" else null, x = [x] + 1],
            each [Valor]),
        Formula = Record.FromList(Lista, ListaNome)
    in
        Formula
    ),
    #"Grupos Expandido" = Table.ExpandRecordColumn(CriaGrupos, "Grupos", ListaNome, ListaNome),
    //Outra função importante, para que os grupos inferiores recebam o nome dos grupos superiores
    #"Preenchido Abaixo" = Table.FillDown(#"Grupos Expandido",ListaNome),
    RemoveArroba = Table.ReplaceValue(#"Preenchido Abaixo","@",null,Replacer.ReplaceValue,ListaNome),
    #"Linhas Filtradas" = Table.SelectRows(RemoveArroba, each ([Tipo] = "A")),
    //Lista quais colunas vão ser mantidas, com base nas colunas originais da planilha e nas colunas de Grupo Criadas
    ColunasImportantes = List.Combine({Table.ColumnNames(Fonte), {"Grupo Analitico"}, ListaNome}),
    #"Manter Colunas Importantes" = Table.SelectColumns(#"Linhas Filtradas",ColunasImportantes),
    //Essa etapa e as próximas servem para tratar planos de contas irregulares.
    #"Tabela Transposta" = Table.Transpose(#"Manter Colunas Importantes"),
    #"Preenchido Abaixo1" = Table.FillDown(#"Tabela Transposta",Table.ColumnNames(#"Tabela Transposta")),
    #"Tabela Transposta1" = Table.Transpose(#"Preenchido Abaixo1"),
    //Lista para voltar os nomes das colunas antes de transpor
    Renomeio = Table.AddColumn(Table.AddIndexColumn(Table.FromList(ColunasImportantes, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Índice", 1, 1, Int64.Type), "Lista", each {"Column" & Text.From([Índice]), [Column1]})[Lista],
    #"Colunas Renomeadas" = Table.RenameColumns(#"Tabela Transposta1",Renomeio),
    //Lista para alterar o tipo de dados da tabela
    AlterarTipos = List.Transform(List.Combine({{ColumnClass}, {ColumnAccountName}, {"Grupo Analitico"}, ListaNome}), each {_, type text}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Colunas Renomeadas",AlterarTipos)
in
    #"Tipo Alterado2"
in
    Fonte