Relatório com colunas diferentes

[Ivan.Info]

Boa tarde...
Me deparei com um relatório onde não evolui no Power query  para resolver o problema.
O relatório me trás despesas com produtos de uma determinada empresa e no final da lista de produtos consumidos o seu total.
Porém, na sequência trás os lançamentos de outras despesas que não são produtos, onde os valores estão na mesma coluna que a quantidade dos produtos lançados acima.

Tentei fazendo uma copia da tabela e separando os lançamentos de produtos das demais despesas, mas quando insiro novos relatórios, o dashboard não atualiza.

Pergunta:
Qual seria a forma correta de tratar esse tipo de relatório para que fique na estrutura correta dos dados sem alterar a planilha gerada pelo sistema?
  

[Joao Barbosa]

9 minutos atrás, Ivan.Info disse:

@Joao Barbosaobrigado pela solução, mas vou precisar das duas informações. 

Creio que essa adaptação do código pode resolver seu caso.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Usuario\Desktop\Custo-01-2022.xlsx"), null, true),
    Plan1_Sheet = Source{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Plan1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SISTEMA - CONSUMO POR CENTRO DE CUSTO", type text}, {"Column2", type text}, {"Column3", type text}, {"Tipo do relatório: ANALÍTICO.", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Cód. Centro", Int64.Type}, {"Nome Centro de Custo", type text}, {"Posto de Coleta", Int64.Type}, {"Data", type date}, {"Código", Int64.Type}, {"Nome Produto/Cliente-Fornec.", type text}, {"Quantidade", type number}, {"Unidade", type text}, {"Valor", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Personalizar", each if [Unidade] = "Total Produtos" then "Produto" else if 
[Unidade] = "Total Contas" then "Despesas" else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Personalizar"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Up", "Valor Real", each if [Valor] = null then [Quantidade] else [Valor]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Unidade] <> "Total Centro de Custo 0023" and [Unidade] <> "Total Contas" and [Unidade] <> "Total Produtos")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Valor"})
in
    #"Removed Columns"

[Nelson Kobayashi]

Olá @Ivan.Info!

Como está sendo feita a importação dos dados para o Power Query ?

São vários arquivos separados em uma pasta ?

Pode mandar amostras dos arquivos com dados fictícios para analisarmos ?

Acredito que o problema esteja no tipo de importação dos dados ou nos cabeçalhos das colunas....

Abç.

[Ivan.Info]

13 horas atrás, Nelson Kobayashi disse:

Olá @Ivan.Info!

Como está sendo feita a importação dos dados para o Power Query ?

São vários arquivos separados em uma pasta ?

Pode mandar amostras dos arquivos com dados fictícios para analisarmos ?

Acredito que o problema esteja no tipo de importação dos dados ou nos cabeçalhos das colunas....

Abç.

Bom dia @Nelson Kobayashi. Os dados estão sendo importados do excel em uma pasta.
Segue exemplo do relatório utilizado.

Desde já obrigado pela ajuda!

Custo-01-2022.xlsx

[Alesson Soares]

mas quando você conecta, vc conecta na pasta ou excel?

[Nelson Kobayashi]

10 horas atrás, Ivan.Info disse:

Bom dia @Nelson Kobayashi. Os dados estão sendo importados do excel em uma pasta.
Segue exemplo do relatório utilizado.

Desde já obrigado pela ajuda!

Custo-01-2022.xlsx 14 kB · 2 downloads

Uma dúvida:

Da linha 25 pra baixo é um outro arquivo excel na mesma pasta ou a atualização é feita no mesmo arquivo excel ?

[Ivan.Info]

14 horas atrás, Nelson Kobayashi disse:

Uma dúvida:

Da linha 25 pra baixo é um outro arquivo excel na mesma pasta ou a atualização é feita no mesmo arquivo excel ?

É no mesmo arquivo. O sistema já me gera dessa forma.

[Matheus M.]

Bom dia, 
Uma forma simples de resolver isso é criar 3 novas colunas, 2 colunas irao conter as informacoes de unidades e valor tratadas e a 3 coluna dira se é um produto ou uma conta. Se a sua base nao for imensa, nao acho que tera problemas de performance.

Voce pode começar tirando as primeiras linhas (acredito que seja padrao do sistema) e subindo cabeçalhos. Depois, eu excluiria todas as linhas com "cod centro" vazio (isso pode ser resolvido com um filtro). Assim, voce elimina seus totais.

Depois, acredito que sempre que for um "produto", voce tera algo preenchido na unidade, correto ?. Se sim:

Quantidade: Tem informaçao em unidades ? repete coluna quantidade. Nao tem informacao unidades ? retorna zero ou vazio

Valor: Tem informaçao unidades (pode ser valor tb) ? repete coluna valor. Nao tem informacao valor (ou unidades se preferir) ? retorna quantidade

Tipo: Tem informacao unidades ? Se sim, é produto. Caso nao tenha, é Conta.

Caso tenha urgência, é o que consegui pensar rapidamente no momento. 

Abç

 

[Nelson Kobayashi]

2 horas atrás, Ivan.Info disse:

É no mesmo arquivo. O sistema já me gera dessa forma.

E quando surgem novos lançamentos de atualização, aonde aparecem os novos dados ?

Será uma nova planilha ou os novos dados são inseridos abaixo dos dados atuais ?

Ou o sistema vai gerando um novo arquivo para substituir o atual ?

Abç.

[Joao Barbosa]

2 horas atrás, Ivan.Info disse:

É no mesmo arquivo. O sistema já me gera dessa forma.

Se precisa so dos produtos pensei nesse código acho que pode ajudar

let
    Source = Excel.Workbook(File.Contents("C:\Users\Usuario\Desktop\Custo-01-2022.xlsx"), null, true),
    Plan1_Sheet = Source{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Plan1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SISTEMA - CONSUMO POR CENTRO DE CUSTO", type text}, {"Column2", type text}, {"Column3", type text}, {"Tipo do relatório: ANALÍTICO.", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Cód. Centro", Int64.Type}, {"Nome Centro de Custo", type text}, {"Posto de Coleta", Int64.Type}, {"Data", type date}, {"Código", Int64.Type}, {"Nome Produto/Cliente-Fornec.", type text}, {"Quantidade", type number}, {"Unidade", type text}, {"Valor", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Personalizar", each if [Unidade] = "Total Produtos" then "Sim" else if 
[Unidade] = "Total Contas" then "Não" else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Personalizar"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Personalizar] = "Sim") and ([Unidade] <> "Total Produtos"))
in
    #"Filtered Rows"

[Nelson Kobayashi]

Olá @Ivan.Info!

Veja se assim resolve pra você :

Segue o arquivo PBIX em anexo.

 

O que fiz aqui foi usar uma coluna condicional (no Power Query, Menu > Adicionar Coluna > Coluna Condicional):

 

Vai abrir uma caixa de diálogo de Coluna Condicional:

1. ) Aqui podemos dar um nome para a nova coluna a ser criada, podemos renomear a coluna depois.

2. ) Aqui vamos escolher a Coluna "Nome Centro de Custo" 

3. ) Aqui podemos escolher, se na coluna "Nome Centro de Custo" tiver escrito "POSTO DE COLETA 01" vamos trazer o valor da coluna "Valor"

4. ) Aqui é o que queremos trazer caso apareça "POSTO DE COLETA 01" ou "POSTO DE COLETA 02", vamos clicar em "Adicionar Cláusula" para criar uma segunda condição para "POSTO DE COLETA 02" e criamos uma segunda linha.

Em "Senão" eu deixei em branco, mas você pode querer um resultado alternativo e colocar nesse campo.

Clique em "OK".

 

Assim será criada uma nova coluna:

 

Agora é só excluir as colunas desnecessárias de "Quantidade", "Unidade" e "Valor" , renomear a coluna "Personalizar" para "Valor" e ;

finalmente mudar o tipo de coluna para número 1.2.

Teste aí e veja se resolve pra você, pode ser que na próxima atualização não funcione, preciso saber mais detalhes de como funciona a atualização do seu projeto.

É isso aí ! Espero ter ajudado.

Custo-01-2022 (Solução Nelson).pbix

[Nelson Kobayashi]

Olá @Ivan.Info!

Pensei numa solução melhor ainda, segue o passo a passo:

Aqui está a base de dados importada da planilha que você mandou.

Primeiro vamos filtrar as linhas vazias a partir da coluna 2 (Nome do Centro de Custo), clicando em “Remover Vazio”. Assim já removemos todas as linhas desnecessárias.

Agora vamos adicionar uma coluna condicional clicando no menu >Adicionar coluna > Coluna Condicional :

Vai abrir uma caixa de diálogo “Adicionar Coluna Condicional”, aqui está o pulo do gato:

1. ) Aqui vamos escolher a “Column9” para usar como parâmetro que irá definir o que vamos trazer para a nossa nova coluna Personalizada.

2. ) Aqui deixamos “Igual a”

3. ) Aqui vamos escrever “null” (sem aspas). Isso significa que se o ETL encontrar linhas vazias (null) na Column9, deverá retornar algo que eu escolher.

4. ) Aqui mude a opção para este ícone de coluna, para habilitar a escolha de uma coluna.

5. ) Quando mudamos o ícone da etapa 4. ) para coluna, aqui vai abrir as colunas que podemos escolher na própria tabela, vamos escolher “Column7”.

A configuração dessa primeira linha diz que “Procure na Column9 ... Se encontrar linhas vazias “null” (b), traga para a nova coluna personalizada os valores da Column7 (a).

6. ) Finalmente, aqui neste campo é o “senão...”. Vamos mudar o ícone para escolher uma coluna e vamos escolher “Column9”.

Isso significa que se o ETL não encontrar o “null” da condição anterior, deverá trazer uma informação alternativa, que neste caso são os próprios valores da “Colunm9” (c).

 

Com isso, conseguimos trazer os valores desejados para um única coluna:

Falta limpar as colunas indesejadas, promover cabeçalho e identar os valores das colunas.

 

Primeiro vamos escolher as colunas que queremos manter:

1. ) Clique no ícone de opções da tabela, fica bem no cantinho superior, logo acima da primeira coluna da tabela.

2. ) Vai abrir as opções de ETL da tabela, selecione “Escolher Colunas...”

Vai abrir uma caixa de diálogo “Escolher Colunas” desmarque as colunas 7, 8 e 9.

Agora vamos promover a primeira linha da tabela para cabeçalho :

1. ) Clique no ícone de opções da tabela, fica bem no cantinho superior, logo acima da primeira coluna da tabela.

2. ) Vai abrir as opções de ETL da tabela, selecione “Usar a primeira linha como cabeçalho”

Pronto ! Fazendo nessa ordem, temos a vantagem que essa etapa já faz o reconhecimento do tipo de dados das colunas automaticamente (Data, Número, ABC). Apenas cheque se os tipos estão corretos.

Com isso temos a tabela desejada com pouquíssimas etapas de ETL aplicadas.

E a vantagem sobre a outra forma que sugeri antes é que desse jeito agora, a coluna condicional funciona em qualquer tabela nas futuras atualizações que surgirem na sua pasta, enquanto que do jeito anterior, só funcionaria para “Posto de Coleta 01” e “Posto de Coleta 02” ... Se surgisse um “Posto de Coleta 03” já não funcionaria.

 

É isso aí ! Espero a dica seja útil.

Se resolveu o seu problema, por favor marque essa resposta como “Melhor Resposta” para ajudar outros colegas a achar a solução e também vai me motivar ainda mais a continuar ajudando os outros colegas aqui no fórum.

 

Solução Nelson Melhorada.pbix

[Hugo Tobias Lima da Silva]

Muito bom.

Queria só perguntar uma coisa rapidamente sem necessidade de um novo tópico. Qual a diferença entre conectar em uma pasta e direto no arquivo em termos de performance?

[Ivan.Info]

Em 12/03/2022 em 00:56, Nelson Kobayashi disse:

Olá @Ivan.Info!

Pensei numa solução melhor ainda, segue o passo a passo:

Aqui está a base de dados importada da planilha que você mandou.

Primeiro vamos filtrar as linhas vazias a partir da coluna 2 (Nome do Centro de Custo), clicando em “Remover Vazio”. Assim já removemos todas as linhas desnecessárias.

 

Agora vamos adicionar uma coluna condicional clicando no menu >Adicionar coluna > Coluna Condicional :

 

Vai abrir uma caixa de diálogo “Adicionar Coluna Condicional”, aqui está o pulo do gato:

1. ) Aqui vamos escolher a “Column9” para usar como parâmetro que irá definir o que vamos trazer para a nossa nova coluna Personalizada.

2. ) Aqui deixamos “Igual a”

3. ) Aqui vamos escrever “null” (sem aspas). Isso significa que se o ETL encontrar linhas vazias (null) na Column9, deverá retornar algo que eu escolher.

4. ) Aqui mude a opção para este ícone de coluna, para habilitar a escolha de uma coluna.

5. ) Quando mudamos o ícone da etapa 4. ) para coluna, aqui vai abrir as colunas que podemos escolher na própria tabela, vamos escolher “Column7”.

A configuração dessa primeira linha diz que “Procure na Column9 ... Se encontrar linhas vazias “null” (b), traga para a nova coluna personalizada os valores da Column7 (a).

6. ) Finalmente, aqui neste campo é o “senão...”. Vamos mudar o ícone para escolher uma coluna e vamos escolher “Column9”.

Isso significa que se o ETL não encontrar o “null” da condição anterior, deverá trazer uma informação alternativa, que neste caso são os próprios valores da “Colunm9” (c).

 

 

Com isso, conseguimos trazer os valores desejados para um única coluna:

Falta limpar as colunas indesejadas, promover cabeçalho e identar os valores das colunas.

 

 

Primeiro vamos escolher as colunas que queremos manter:

1. ) Clique no ícone de opções da tabela, fica bem no cantinho superior, logo acima da primeira coluna da tabela.

2. ) Vai abrir as opções de ETL da tabela, selecione “Escolher Colunas...”

 

Vai abrir uma caixa de diálogo “Escolher Colunas” desmarque as colunas 7, 8 e 9.

 

Agora vamos promover a primeira linha da tabela para cabeçalho :

1. ) Clique no ícone de opções da tabela, fica bem no cantinho superior, logo acima da primeira coluna da tabela.

2. ) Vai abrir as opções de ETL da tabela, selecione “Usar a primeira linha como cabeçalho”

 

Pronto ! Fazendo nessa ordem, temos a vantagem que essa etapa já faz o reconhecimento do tipo de dados das colunas automaticamente (Data, Número, ABC). Apenas cheque se os tipos estão corretos.

Com isso temos a tabela desejada com pouquíssimas etapas de ETL aplicadas.

E a vantagem sobre a outra forma que sugeri antes é que desse jeito agora, a coluna condicional funciona em qualquer tabela nas futuras atualizações que surgirem na sua pasta, enquanto que do jeito anterior, só funcionaria para “Posto de Coleta 01” e “Posto de Coleta 02” ... Se surgisse um “Posto de Coleta 03” já não funcionaria.

 

 

É isso aí ! Espero a dica seja útil.

Se resolveu o seu problema, por favor marque essa resposta como “Melhor Resposta” para ajudar outros colegas a achar a solução e também vai me motivar ainda mais a continuar ajudando os outros colegas aqui no fórum.

 

Solução Nelson Melhorada.pbix 59 kB · 0 downloads

@Nelson Kobayashi obrigado pela solução enviada. Da forma apresentada não consigo diferenciar os custos operacionais dos custos de produtos. Mas entendi perfeitamente o logica utilizada.
Muito obrigado mais uma vez.

[Ivan.Info]

Em 11/03/2022 em 09:31, Matheus M. disse:

Bom dia, 
Uma forma simples de resolver isso é criar 3 novas colunas, 2 colunas irao conter as informacoes de unidades e valor tratadas e a 3 coluna dira se é um produto ou uma conta. Se a sua base nao for imensa, nao acho que tera problemas de performance.

Voce pode começar tirando as primeiras linhas (acredito que seja padrao do sistema) e subindo cabeçalhos. Depois, eu excluiria todas as linhas com "cod centro" vazio (isso pode ser resolvido com um filtro). Assim, voce elimina seus totais.

Depois, acredito que sempre que for um "produto", voce tera algo preenchido na unidade, correto ?. Se sim:

Quantidade: Tem informaçao em unidades ? repete coluna quantidade. Nao tem informacao unidades ? retorna zero ou vazio

Valor: Tem informaçao unidades (pode ser valor tb) ? repete coluna valor. Nao tem informacao valor (ou unidades se preferir) ? retorna quantidade

Tipo: Tem informacao unidades ? Se sim, é produto. Caso nao tenha, é Conta.

Caso tenha urgência, é o que consegui pensar rapidamente no momento. 

Abç

 

@Matheus M.obrigado pela ideia. Vou seguir e tentar encontrar uma solução.

[Ivan.Info]

Em 11/03/2022 em 10:37, Joao Barbosa disse:

Se precisa so dos produtos pensei nesse código acho que pode ajudar

let
    Source = Excel.Workbook(File.Contents("C:\Users\Usuario\Desktop\Custo-01-2022.xlsx"), null, true),
    Plan1_Sheet = Source{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Plan1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SISTEMA - CONSUMO POR CENTRO DE CUSTO", type text}, {"Column2", type text}, {"Column3", type text}, {"Tipo do relatório: ANALÍTICO.", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Cód. Centro", Int64.Type}, {"Nome Centro de Custo", type text}, {"Posto de Coleta", Int64.Type}, {"Data", type date}, {"Código", Int64.Type}, {"Nome Produto/Cliente-Fornec.", type text}, {"Quantidade", type number}, {"Unidade", type text}, {"Valor", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Personalizar", each if [Unidade] = "Total Produtos" then "Sim" else if 
[Unidade] = "Total Contas" then "Não" else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Personalizar"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Personalizar] = "Sim") and ([Unidade] <> "Total Produtos"))
in
    #"Filtered Rows"

@Joao Barbosaobrigado pela solução, mas vou precisar das duas informações.