ETL em tabela de excel com colunas separadas por ponto e vírgula

[Rafa Lemos]

Tenho uma tabela em excel com várias colunas com as informações separadas por ponto e vírgula.

Essa planilha mostra os fornecedores e os produtos que cada CNPJ possui.

Ex.: O CNPJ 88843722805256 tem 6 fornecedores.

O nome dos fornecedores estão na coluna “Fornecedor”, e cada um está separado por ;

Na próxima coluna tem o código do fornecedor também separado por ; e por ordem do Fornecedor.

Ex: ODONTOPREV S A é o primeiro fornecedor da coluna;

Na coluna “código do fornecedor” o primeiro código é 301949 da ODONTOPREV S A.

E assim funciona tbm para as colunas de produtos.

Como posso fazer para tratar isso no powerbi, para que os fornecedores fiquem todos em uma coluna, assim seu código, o produto e código do produto?

Segue a planilha em anexo.

EXEMPLO_PBI.xlsx

[Rafa Lemos]

Boa noite!

Segue script do Power Query:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\rafae\Downloads\EXEMPLO PBI.xlsx"), null, true),
    Plan1_Sheet = Source{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Plan1_Sheet, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","[","",Replacer.ReplaceText,{"Empresa", "Código da Empresa", "Código do Produto", "Produto"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Empresa", "Código da Empresa", "Código do Produto", "Produto"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Empresa", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Empresa.1", "Empresa.2", "Empresa.3", "Empresa.4", "Empresa.5", "Empresa.6", "Empresa.7", "Empresa.8", "Empresa.9", "Empresa.10", "Empresa.11"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Código da Empresa", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Código da Empresa.1", "Código da Empresa.2", "Código da Empresa.3", "Código da Empresa.4", "Código da Empresa.5", "Código da Empresa.6", "Código da Empresa.7", "Código da Empresa.8", "Código da Empresa.9", "Código da Empresa.10", "Código da Empresa.11"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"CNPJ", "Código da Empresa.1", "Código da Empresa.2", "Código da Empresa.3", "Código da Empresa.4", "Código da Empresa.5", "Código da Empresa.6", "Código da Empresa.7", "Código da Empresa.8", "Código da Empresa.9", "Código da Empresa.10", "Código da Empresa.11"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"CNPJ", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Columns1", {{"Attribute", each Text.AfterDelimiter(_, "."), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Attribute.1", each Text.AfterDelimiter(_, "."), type text}}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Text Before Delimiter", "TrueFalse Empres", each [Attribute.1] = [Attribute]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([TrueFalse Empres] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute", "Attribute.1"})
in
    #"Removed Columns"

Só precisa alterar a fonte de dados.

Fiz esse exemplo somente com as empresas.
Não sei se os produtos serão em outra tabela, porque da forma que está, os produtos repetiriam para cada empresa.