Estruturação de Dados - Power Query

[Gabriel Canadeu]

Bom dia Galera !! 

Estou trabalhando com uma base de dados, na qual preciso realizar alguns tratamentos para que as informações fiquem organizadas corretamente no Power Query. 

Estou importando uma tabela dinâmica onde preciso transformar colunas em linhas. Algumas modificações eu consegui realizar, porém o caso abaixo não sei como processeguir.

Preciso que os dados de datas fiquem em colunas, assim como: Budget Cash-In/Valor Cash-In. 

Ou seja, estou tentando chegar no seguinte resultado:

Coluna: Data

Linhas: Datas das respectivas ações

Coluna: Budget Cashin

Linhas: Valores

Coluna: Valor Cashin

Linhas: Valores

Coluna: Clientes

Linhas: Nome dos clientes

PowerQuery.xlsx

[Vitor Peralva]

1 hora atrás, Gabriel Canadeu disse:

@Vitor Peralva

 

Analisando melhor, eu consigo resolver metade do problema, alterando a organização da tabela dinâmica, correto ? assim, cheguei ao seguinte resultado. [Imagem abaixo]

 

Porém, preciso criar duas novas colunas. 

Coluna 1 = Budget 

Linhas  = Valores 

Coluna = Cash-in

Linhas = Valores

Apenas, utilizando a função "transpor" eu não chego ao resultado, na verdade acaba bagunçando tudo. Estou pesquisando cases na Internet que possua o mesmo desafio. se tiver sugestões, agradeço!!!! 

Vlw!.

PowerQurey(2).xlsx 14 kB · 0 downloads

 

Você tem uma tabela organizada de um modo diferente, o que envolve um ETL diferente.

Estou partindo do pressuposto de que vai trabalhar no Power BI, logo, você só precisaria da coluna de data.

Na sua dCalendario, você pode colocar as outras colunas com ano, semestre, trimestre, mês, etc.

Depois criar o relacionamento.

 

O meu PBI está em Inglês, o que pode gerar um diferença na nomenclatura dos comandos.

Importei o arquivo e o Power Query gera automaticamente algumas etapas:

 

Vamos excluir a última etapa de Tipo Alterado (Changed Type).

Basta clicar no X no lado esquerdo da etapa.

Na Guia Página Inicial, você tem a opção de Escolher Colunas:

 

Vamos marcar as colunas que nos interessam, no caso, Data, Unidade, Negócio, Cliente, Valores, Column10.

E dar OK.

 

Vamos selecionar as colunas de Data, Unidade, Negócio e Cliente e realizar o Preenchimento Para Baixo:

 

Selecione a coluna Valores e, depois, na Guia Transformar, a opção de Coluna Dinâmica.

Como coluna de valor, selecione a Column10 (Coluna 10).

Dê OK.

 

Imagino  que este seja o resultado que espera.

Bastaria renomear o nome das colunas para ficarem adequadas as suas regras de negócio e determinar o tipo.

O meu código até o ponto da imagem acima ficou:

 

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\PowerQurey(2).xlsx"), null, true),
    Plan1_Sheet = Source{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Plan1_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Data", "Unidade", "Negócio", "Cliente", "Valores", "Column10"}),
    #"Filled Down" = Table.FillDown(#"Removed Other Columns",{"Data", "Unidade", "Negócio", "Cliente"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Valores]), "Valores", "Column10", List.Sum)
in
    #"Pivoted Column"

 

Espero que as imagens ter permitam acompanhar melhor a solução.

Espero, também, que ela se demonstre adequada ao seu problema.

[Vitor Peralva]

Boa Noite, @Gabriel Canadeu!

 

Vou apresentar uma sugestão para sua análise, mantive a coluna de Unidade e Negócio, porque não tinha certeza se queria de fato eliminar estas colunas.

Fiz ela rapidamente, então, não pude revisar, imagino que eventualmente ainda seja possível uma ou outra otimização.

O resultado que obtive foi o seguinte:

 

Segue o código utilizado, bastaria alterar o caminho para aplicar ao seu arquivo.

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\PowerQuery.xlsx"), null, true),
    Plan1_Sheet = Source{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Filled Down" = Table.FillDown(Plan1_Sheet,{"Column1", "Column2", "Column3"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Total Geral")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Filled Down1" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    #"Merged Columns1" = Table.CombineColumns(#"Filled Down1",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {";;Unidade", ";;Negócio", ";;Cliente"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Mês", "Date", "Tipo"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{";;Unidade", "Unidade"}, {";;Negócio", "Negócio"}, {";;Cliente", "Cliente"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Data", each if Date.Month(Date.From([Mês])) = Date.Month(Date.From([Date])) then Date.From([Date]) else null),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Tipo]), "Tipo", "Value", List.Sum),
    #"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Unidade", "Negócio", "Cliente", "Mês", "Data", "budgetCashin", "Valor Cashin"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Unidade", type text}, {"Negócio", type text}, {"Cliente", type text}, {"Mês", type text}, {"Data", type date}, {"budgetCashin", Int64.Type}, {"Valor Cashin", Int64.Type}})
in
    #"Changed Type"

 

Você vai ver que tive que fazer um ajuste nas datas, porque para os meses superiores ao da data atual não existe Data, somente mês.

Na expectativa de ter lhe ajudado.

[Gabriel Canadeu]

Boa noite @Vitor Peralva !! 

Show, Obrigado pelo apoio ! 

Repliquei o código sugerido acima no editor avançado, e analisando etapa por entapa, quando chega em "Unipivoted Other Columns" aparece um erro dizendo não encontrar a tabela "unidade".  sabe o que pode ser ? vi que na sua solução acima a coluna aparece normalmente. 

Quanto as datas. eu estou trabalhando com uma tabela dinâmica do Cubo ledger, possivelmente quando enviei o arquivo, enviei fechado por mês e não aberto por dia, por isso deve ter aparecido o mês fechado. 

A próposito, você entende que trabalhando com uma tabela dinâmica, esse metédo que estou querendo utilizar é o correto ?

Editado Maio 19, 2021 por Gabriel Canadeu

[Gabriel Canadeu]

@Vitor Peralva

 

Analisando melhor, eu consigo resolver metade do problema, alterando a organização da tabela dinâmica, correto ? assim, cheguei ao seguinte resultado. [Imagem abaixo]

 

Porém, preciso criar duas novas colunas. 

Coluna 1 = Budget 

Linhas  = Valores 

Coluna = Cash-in

Linhas = Valores

Apenas, utilizando a função "transpor" eu não chego ao resultado, na verdade acaba bagunçando tudo. Estou pesquisando cases na Internet que possua o mesmo desafio. se tiver sugestões, agradeço!!!! 

Vlw!.

PowerQurey(2).xlsx

[Vitor Peralva]

2 horas atrás, Gabriel Canadeu disse:

Boa noite @Vitor Peralva !! 

Show, Obrigado pelo apoio ! 

Repliquei o código sugerido acima no editor avançado, e analisando etapa por entapa, quando chega em "Unipivoted Other Columns" aparece um erro dizendo não encontrar a tabela "unidade".  sabe o que pode ser ? vi que na sua solução acima a coluna aparece normalmente. 

Quanto as datas. eu estou trabalhando com uma tabela dinâmica do Cubo ledger, possivelmente quando enviei o arquivo, enviei fechado por mês e não aberto por dia, por isso deve ter aparecido o mês fechado. 

A próposito, você entende que trabalhando com uma tabela dinâmica, esse metédo que estou querendo utilizar é o correto ?

Olhando a sua solução, observo que, diferentemente da solução proposta, você fez uma promoção de cabeçalhos logo no início, além de um tipo alterado.

Isto pode ter gerado a inconsistência.

Observe como está a sua coluna de Unidade na etapa anterior.

 

Faça um teste, crie uma Consulta Nula.

Cole o código que apresentei e substitua exclusivamente o caminho do arquivo.

Veja se o resultado é apresentado.

 

Sobre a questão de conexão a Tabela Dinâmica, pense da seguinte maneira.

Você precisará acessar o seu arquivo em Excel para atualizar a tabela dinâmica ou exportar o arquivo Excel do sistema, não ficou claro como faz e, somente depois, poderá acessar o Power BI e atualizar nele.

Desta maneira, o ideal seria realizar a conexão direta do Power Query a sua fonte de dados e se ela permitir a criação de Views, melhor ainda, porque você poderia trazer as tabelas prontas do servidor, o que tornaria a atualização muito mais rápida.

 

 

 

 

[Frank Figueredo]

@Gabriel Canadeu a solução proposta pelo @Vitor Peralva resolveu o seu problema?