Importação Direta de Arquivos OFX no Power BI

O Power BI não está preparado para lidar de forma nativa com a importação de arquivos OFX, um padrão usado por muitos bancos para envio de extratos. No entanto, isso não significa que não possamos importar esses arquivos diretamente. Com um pouco de criatividade, é possível resolver esse problema e criar um leitor de OFX sem a necessidade de conversões para CSV ou XML em programas externos.

Abaixo, apresento um script em M Language que realiza a leitura de um arquivo OFX do Banco do Brasil. Este exemplo pode servir de inspiração para lidar com esse tipo de arquivo:

let

    pFile = "C:\temp\Extrato.ofx",

    Fonte = Csv.Document(File.Contents(pFile),[Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Column1", type text}}),

    Mod001 =
      Table.ReorderColumns(
            Table.FillDown(
        Table.ReplaceValue(
      Table.AddIndexColumn(
       Table.RemoveColumns(
          Table.SelectRows(
           Table.AddColumn(
    Table.TransformColumns(
           Table.AddColumn(
           Table.AddColumn( Table.SelectRows(#"Tipo Alterado", each Text.Contains([#"Column1"],"<"))
                          , "Column2", each Text.BetweenDelimiters([Column1],"<",">"), type text)
                          , "Column3", each Text.AfterDelimiter([Column1],">"), type text)
                          , {{"Column3", each try Text.BeforeDelimiter(_,"<")  otherwise _ , type text}})
                          , "Column4", each if List.Contains({"SIGNONMSGSRSV1","BANKMSGSRSV1","STMTTRN","LEDGERBAL"},[Column2]) then [Column2] else null, type text)
                          , each Text.StartsWith(Text.Trim([Column1]) ,"</") = false and [Column1] <> "<OFX>")
                          , {"Column1"})
                          , "Column1",1,1)
                          , each [Column1] , each if [Column4] = null then null else [Column1], Replacer.ReplaceValue, {"Column1"})
                          , {"Column4","Column1"})
                          , {"Column1","Column2","Column3","Column4"}),


    getData = (dbOFX,pBloco,pID) =>
    Table.PromoteHeaders(
         Table.Transpose(
        Table.SelectRows( dbOFX
                        , each [Column4] = pBloco and [Column1] = pID and [Column3]<>"")[[Column2],[Column3]])
                        ),

    Mod002 = 
    Table.AddColumn(
        Table.Group( Mod001 
                   , {"Column1","Column4"}
                   , {{"Data", each _, type table [Column1=number, Column2=text, Column3=text, Column4=text]}})
                   , "Data1", each getData([Data],[Column4],[Column1]), type table)[[Column4],[Data1]]
in
    Mod002

Vamos detalhar o nosso código.

1 – Definição do Arquivo e Leitura:

let
    pFile = "C:\temp\Extrato.ofx",
    Fonte = Csv.Document(File.Contents(pFile), [Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte, {{"Column1", type text}}),

• pFile: Define o caminho do arquivo OFX.
• Fonte: Lê o conteúdo do arquivo como um documento CSV com codificação 1252 (ANSI) e sem estilos de citação.
• #”Tipo Alterado”: Transforma o tipo da coluna Column1 para texto.

2 – Transformação dos Dados:

    Mod001 =
      Table.ReorderColumns(
            Table.FillDown(
        Table.ReplaceValue(
      Table.AddIndexColumn(
       Table.RemoveColumns(
          Table.SelectRows(
           Table.AddColumn(
    Table.TransformColumns(
           Table.AddColumn(
           Table.AddColumn( Table.SelectRows(#"Tipo Alterado", each Text.Contains([#"Column1"],"<"))
                          , "Column2", each Text.BetweenDelimiters([Column1],"<",">"), type text)
                          , "Column3", each Text.AfterDelimiter([Column1],">"), type text)
                          , {{"Column3", each try Text.BeforeDelimiter(_,"<")  otherwise _ , type text}})
                          , "Column4", each if List.Contains({"SIGNONMSGSRSV1","BANKMSGSRSV1","STMTTRN","LEDGERBAL"},[Column2]) then [Column2] else null, type text)
                          , each Text.StartsWith(Text.Trim([Column1]) ,"</") = false and [Column1] <> "<OFX>")
                          , {"Column1"})
                          , "Column1",1,1)
                          , each [Column1] , each if [Column4] = null then null else [Column1], Replacer.ReplaceValue, {"Column1"})
                          , {"Column4","Column1"})
                          , {"Column1","Column2","Column3","Column4"}),

• Mod001: Realiza uma série de transformações nos dados:
  • Table.SelectRows: Seleciona apenas as linhas que contêm tags XML (<), excluindo linhas de fechamento (</) e a tag <OFX>.
  • Table.AddColumn: Cria colunas adicionais (Column2, Column3) para extrair informações entre delimitadores < > e após >.
  • Table.TransformColumns: Manipula a coluna Column3, tentando extrair o conteúdo antes do delimitador <, se possível.
  • Table.ReplaceValue: Substitui valores na coluna Column1 com base em condições específicas.
  • Table.AddIndexColumn: Adiciona uma coluna de índice às tabelas resultantes.
  • Table.RemoveColumns: Remove colunas não necessárias.
  • Table.FillDown: Preenche valores para baixo em colunas onde os valores são nulos.
  • Table.ReorderColumns: Reordena as colunas para uma ordem específica.

3 – Função personalizada getData:

    getData = (dbOFX,pBloco,pID) =>
    Table.PromoteHeaders(
         Table.Transpose(
        Table.SelectRows( dbOFX
                        , each [Column4] = pBloco and [Column1] = pID and [Column3]<>"")[[Column2],[Column3]])
                        ),

• getData: Define uma função que recebe três parâmetros (dbOFX, pBloco, pID):
  • Table.SelectRows: Seleciona as linhas onde Column4 é igual a pBloco, Column1 é igual a pID e Column3 não está vazio.
  • Table.Transpose: Transpõe a tabela resultante.
  • Table.PromoteHeaders: Promove a primeira linha como cabeçalhos da tabela.

4 – Combinando e Agrupando os Dados:

    Mod002 = 
    Table.AddColumn(
        Table.Group( Mod001 
                   , {"Column1","Column4"}
                   , {{"Data", each _, type table [Column1=number, Column2=text, Column3=text, Column4=text]}})
                   , "Data1", each getData([Data],[Column4],[Column1]), type table)[[Column4],[Data1]]

• Mod002: Agrupa os dados de Mod001 usando Table.Group, agrupando por Column1 e Column4.
  • Table.AddColumn: Adiciona uma coluna calculada Data1, que aplica a função getData aos grupos de dados.
  • Retorna apenas as colunas Column4 e Data1 como resultado final.

5 – Retorno do Resultado Final:

in
    Mod002

• Retorna Mod002 como o resultado final do script.

Este script em M Language é projetado para processar arquivos OFX do Banco do Brasil, realizando várias transformações nos dados para prepará-los para análise posterior no Power BI. Ele extrai informações específicas de tags OFX, promove cabeçalhos e organiza os dados em uma estrutura adequada para análise e visualização.

Desejamos que o conteúdo tenha sido útil para o seu aprendizado.
– essa foi mais uma dica do Ornit 😉.