Ir para conteúdo
  • 0

Vários tipos de dados em uma coluna excel


Thiago Anselmo
Ir para solução Solucionado por Vitor Peralva ,

Pergunta

  • Alunos

Pessoal, boa tarde.

Estou com um problema na construção de um dataset. Preciso disponibilizar um dataset que contenha os valores realizados e metas de vários indicadores. No excel esse indicadore estão disponibilizados em linhas e seus valores em uma coluna. O problema é que nessa coluna tenho vários tipos de dados como datas, horas, valores decimais, valores inteiros, porcentagens, etc.

A maioria dessas colunas tem fórmulas personalizadas para apresentar os valore dessa forma no excel. Quando trago para o power bi, os valores vem em diversas formas diferentes e acaba corrompendo. 

Alguém consegue dar um sugestão de como fazer que uma tabela no power BI apresente os dados da mesma forma que no excel?

Já tentei pivotar, transpor, colunas de exemplo, coluna condicional.. Tentei no dax, no M mas não estou conseguindo resolver.

Preciso entregar esse dataset amanhã e já está batendo um desespero rs..

Segue imagem de exemplo:
 

image.png.2f78dcc31dbe071afec099a3b0944bba.png

Link para o comentário
Compartilhar em outros sites

17 respostass a esta questão

Posts Recomendados

  • 0
  • Alunos
  • Solução
Em 17/07/2022 em 10:50, Thiago Anselmo disse:

@Vitor Peralva bom dia.

Obrigado por ajudar.

Você chegou no resultado demonstrado apenas aplicando o código na coluna para formato da hora? Fez alguma tratativa para os demais tipos de dados?

Tentei aplicar por aqui mas não tive sucesso. Se puder me auxiliar agradeço bastante, já estou há algumas semanas apanhando desse projeto.

 

 

 

Sim, apliquei para as colunas que listei, mas não usei todas no visual.

Segue PBIX para análise.

Dúvida - Thiago Anselmo.pbix

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos
Agora, Alexandre H. Moraes disse:

Cara, não entendi muito bem seu problemas, mas vamos lá... Percebi que existem 3 colunas referente as notas. Notas 7, 10 e 15 tenta fazer um unpivot (Transformar colunas em linhas) não sei se você já fez isso com essas colunas, mas veja se da certo.

Boa tarde Alexandre.

Sim, já tentei o unpivot mas também não tive sucesso. 

O maior problema é que quando o Power BI importa os dados do xls ele muda os tipos dos valores, como demonstrado abaixo. 
Por exemplo, os valores que contem apenas horas no excel, ele tras com data e hora. Os valores de porcentagem (Ex: 90%) ele trás como decimal. Valores monetários, ele considera como texto.  E por aí vai...

image.png.15176060f4ed4b9ea38eddcb0cdd52e9.png

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

Bom dia,

@Thiago Anselmo no Power BI cada coluna deve ter um tipo de dado. Se no excel em uma única coluna há vários tipos de dados você terá que ver a regra aplicada e transforma essa coluna com diversos tipo de dados em varias colunas com um único tipo de dado.

Ex: Na coluna Realizado Acumulado identifiquei que há linhas com tipo: Data/ hora, hora e valores decimais. Você terá que transformar essa coluna em 3 colunas com um único tipo de dado. Se puder disponibilizar o dataset ou pelo menos parte dele a comunidade poderá ajudar mais.

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos
19 minutos atrás, joaomarraes disse:

Bom dia,

@Thiago Anselmo no Power BI cada coluna deve ter um tipo de dado. Se no excel em uma única coluna há vários tipos de dados você terá que ver a regra aplicada e transforma essa coluna com diversos tipo de dados em varias colunas com um único tipo de dado.

Ex: Na coluna Realizado Acumulado identifiquei que há linhas com tipo: Data/ hora, hora e valores decimais. Você terá que transformar essa coluna em 3 colunas com um único tipo de dado. Se puder disponibilizar o dataset ou pelo menos parte dele a comunidade poderá ajudar mais.

Bom dia João.

Sim, parti dessa linha de pensamento também, tentei criar uma coluna para cada tipo, mas como são muitos indicadores não consegui chegar em um resultado correto até o momento.

Estou enviando um exemplo do dataset em anexo, caso alguém consiga ajudar, agradeço bastante.

Metas_Mai.22.xlsx

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

Boa tarde, Cara, usa a unidade de medida para identificar os tipo, eu acho que vai ser difícil usar tipos diferentes na mesma coluna, cada coluna tem de ter um tipo.

usa sua coluna de "unidade de medida" para fazer um condicional. 

image.png.8b90cb1758655a617343d831c2a059a4.png

Por exemplo, dessa forma. 

image.png.4a748998c131994626b2bb6765b02480.png

 

ai faz mais duas colunas uma pra taxa e outra para percentual, se alguma for mesmo tipo, ai pode agrupar na mesma coluna. 

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\wever\Downloads\Metas_Mai.22.xlsx"), null, true),
    Oficial_Sheet = Fonte{[Item="Oficial",Kind="Sheet"]}[Data],
    #"Linhas Filtradas" = Table.SelectRows(Oficial_Sheet, each ([Column1] <> null)),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Linhas Filtradas", [PromoteAllScalars=true]),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Cabeçalhos Promovidos",{"Unidade de Medida", "Nota 7"}),
    #"Personalização Adicionada" = Table.AddColumn(#"Outras Colunas Removidas", "Nota7 DATA", each if [Unidade de Medida] = "Horas" then DateTime.From([Nota 7]) else "", type datetime),
    #"Personalização Adicionada1" = Table.AddColumn(#"Personalização Adicionada", "Nota7 Monetario", each if [Unidade de Medida] = "R$" then Decimal.From([Nota 7]) else "", type number),
    #"Personalização Adicionada2" = Table.AddColumn(#"Personalização Adicionada1", "Nota7 Inteiro", each if [Unidade de Medida] = "Nota" then Number.From([Nota 7]) else "", Int64.Type)
in
    #"Personalização Adicionada2"


Eu removi as outras colunas só para ficar melhor no exemplo, mais seria essa lógica 

 

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

Em relação a alteração que o power query faz automatica dos tipos é uma configuração pode desmarca-la ou quando importar excluir a etapa de alteração dos tipos, para fazer o seu tratamento já que ele terá essas condicionais, no caso da hora por exemplo para importar apenas o valor da hora, sem transformar em data, vc pode usar a extração de hora. com Time.Hour direto na hora de criar a coluna.



image.png.8c3acbfe2ace2ddd2a3f294fa16493a3.png

 

Ou pode extrair depois. o importante é que tendo cada coluna separada no seu tipo, vc terá como fazer a tratativa para cada type. 

image.png.30ae73f2dfd38f6713067631dea11428.png

image.png.e5562043e5b24e70e10cf13660f5aafe.png

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos
20 minutos atrás, Weverton Todeschini disse:

Em relação a alteração que o power query faz automatica dos tipos é uma configuração pode desmarca-la ou quando importar excluir a etapa de alteração dos tipos, para fazer o seu tratamento já que ele terá essas condicionais, no caso da hora por exemplo para importar apenas o valor da hora, sem transformar em data, vc pode usar a extração de hora. com Time.Hour direto na hora de criar a coluna.



image.png.8c3acbfe2ace2ddd2a3f294fa16493a3.png

 

Ou pode extrair depois. o importante é que tendo cada coluna separada no seu tipo, vc terá como fazer a tratativa para cada type. 

image.png.30ae73f2dfd38f6713067631dea11428.png

image.png.e5562043e5b24e70e10cf13660f5aafe.png

Weverton, boa tarde.

Primeiramente, obrigado por tentar ajudar.

Então, também tentei o processo de fazer o pivô das colunas utilizando o campo Unidade de Medida como parâmetro.  Tentei de algumas formas, transformando as colunas em linhas e também utilizando a opção de coluna dinâmica.

image.png.5f2429e53fbf5acfa54629ff9973840d.png

 

O resultado é o mesmo. Minha ideia era separar esses dados por colunas com cada Unidade de Medida, classificar cada uma com seu respectivo tipo de dado e posteriormente criar uma coluna condicional que me trouxesse o valor de cada linha baseado na Unidade de Medida. Porém como alguns tipos de Unidade de Medida possuem o mesmo tipo de dado, não consegui criar a condicional para trazer o valor correto daquela linha.

Quanto a detecção automática do tipo de dado, também cheguei a desabilitar nas configurações do Power BI.

Para tentar prosseguir, cheguei a criar colunas de exemplos baseados nas colunas pivotadas, retirando as datas dos dados de horas por exemplo como demonstrado abaixo.
O problema foi que o Power BI passou a identificar tipos inteiros como textos, trazendo valores monetários errados.

image.png.926cd379a24f644143e8452a771cad84.png

 

let
    Fonte = Folder.Files("G:\1. Processos e Indicadores\2. Indicadores"),
    #"Linhas Filtradas" = Table.SelectRows(Fonte, each ([Extension] = ".xlsx")),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Linhas Filtradas",{"Content", "Name"}),
    #"Linhas Filtradas1" = Table.SelectRows(#"Outras Colunas Removidas", each Text.StartsWith([Name], "Metas")),
    #"Linhas Filtradas2" = Table.SelectRows(#"Linhas Filtradas1", each ([Name] <> "Metas_GEINF_Carga Sistema.xlsx" and [Name] <> "Metas_GEINF_Carga Sistemav1.xlsx" and [Name] <> "Metas_GEINF_Carga Sistemav2.xlsx" and [Name] <> "Metas_GEINF_Carga Sistema_Vanessa.xlsx")),
    #"Personalização Adicionada" = Table.AddColumn(#"Linhas Filtradas2", "Conteudo", each Excel.Workbook([Content])),
    #"Colunas Removidas" = Table.RemoveColumns(#"Personalização Adicionada",{"Content"}),
    #"Conteudo Expandido" = Table.ExpandTableColumn(#"Colunas Removidas", "Conteudo", {"Data"}, {"Data"}),
    #"Data Expandido" = Table.ExpandTableColumn(#"Conteudo Expandido", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}),
    #"Linhas Filtradas3" = Table.SelectRows(#"Data Expandido", each ([Column1] <> null)),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Linhas Filtradas3", [PromoteAllScalars=true]),
    #"Colunas Removidas3" = Table.RemoveColumns(#"Cabeçalhos Promovidos",{"Cód Indicador"}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Colunas Removidas3",{{Table.ColumnNames(#"Colunas Removidas3"){0}, "Arquivo"}}),
    #"Linhas Filtradas4" = Table.SelectRows(#"Colunas Renomeadas", each ([COD AREA] = "1.5.4" or [COD AREA] = "1.5.4.1" or [COD AREA] = "1.5.4.1.1" or [COD AREA] = "1.5.4.1.2" or [COD AREA] = "1.5.4.2" or [COD AREA] = "1.5.5" or [COD AREA] = "1.5.5.1" or [COD AREA] = "1.5.5.2" or [COD AREA] = "1.5.5.2.1" or [COD AREA] = "1.5.5.2.1.1" or [COD AREA] = "1.5.5.2.1.2" or [COD AREA] = "1.5.5.2.2" or [COD AREA] = "1.5.5.2.2.1" or [COD AREA] = "1.5.5.2.2.2" or [COD AREA] = "1.5.6")),
    #"Texto em Maiúscula" = Table.TransformColumns(#"Linhas Filtradas4",{{"Nome Indicador", Text.Upper, type text}}),
    #"Colunas Removidas1" = Table.RemoveColumns(#"Texto em Maiúscula",{"Arquivo"}),
    #"Consultas Mescladas" = Table.NestedJoin(#"Colunas Removidas1", {"Nome Indicador"}, Indicadores, {"Nome Indicador"}, "Indicadores", JoinKind.LeftOuter),
    #"Indicadores Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas", "Indicadores", {"IdIndicador"}, {"IdIndicador"}),
    #"Consultas Mescladas1" = Table.NestedJoin(#"Indicadores Expandido", {"COD AREA"}, dArea, {"COD AREA"}, "dArea", JoinKind.LeftOuter),
    #"dArea Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas1", "dArea", {"IdArea"}, {"IdArea"}),
    #"Colunas Removidas2" = Table.RemoveColumns(#"dArea Expandido",{"COD AREA", "MACRO AREA", "AREA", "MATR RESP/ COD LOJA", "COD AREA SUPERIOR", "AREA SUPERIOR", "Nome Indicador"}),
    #"Últimos caracteres inseridos" = Table.AddColumn(#"Colunas Removidas2", "Nota7", each Text.End(Text.From([Nota 7], "pt-BR"), 8), type text),
    #"Últimos caracteres inseridos1" = Table.AddColumn(#"Últimos caracteres inseridos", "Nota10", each Text.End(Text.From([Nota 10], "pt-BR"), 8), type text),
    #"Últimos caracteres inseridos2" = Table.AddColumn(#"Últimos caracteres inseridos1", "Nota15", each Text.End(Text.From([Nota 15], "pt-BR"), 8), type text),
    #"Coluna Personalizada Adicionada" = Table.AddColumn(#"Últimos caracteres inseridos2", "META", each let splitMeta = List.Reverse(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(Text.From([Meta], "pt-BR"))) in Text.Start(splitMeta{0}?, 10), type text),
    #"Coluna Personalizada Adicionada1" = Table.AddColumn(#"Coluna Personalizada Adicionada", "Personalizar", each let splitRealizado = List.Reverse(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(Text.From([Realizado], "pt-BR"))) in Text.Start(splitRealizado{0}?, 10), type text),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Coluna Personalizada Adicionada1",{{"Personalizar", "REALIZADO_"}}),
    #"Coluna Personalizada Adicionada2" = Table.AddColumn(#"Colunas Renomeadas1", "REALIZADO_ACUMULADO", each let splitRealizadoacumulado = List.Reverse(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(Text.From([Realizado acumulado], "pt-BR"))) in Text.Start(splitRealizadoacumulado{0}?, 10), type text),
    #"Colunas Removidas4" = Table.RemoveColumns(#"Coluna Personalizada Adicionada2",{"Nota 7", "Nota 10", "Nota 15", "Meta", "Realizado", "Realizado acumulado"}),
    #"Duplicatas Removidas" = Table.Distinct(#"Colunas Removidas4")
in
    #"Duplicatas Removidas"

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

@Thiago Anselmo não sugeri fazer o pivô, pelo que entendi do problema até agora, no excel vc tem uma coluna para demostrar tipos de analises diferentes, o que eu sugeri foi destrinchar 1 coluna, por exemplo nota(7) em 5 novas colunas cada uma com seu respectivo tipo de dados. adicionando colunas personalizadas fazendo a checagem de cada unidade de medida, conforme o exemplo. mais se ainda não for isso, talvez seja melhor postar o pbix com o exemplo final de como teria de ficar seus visuais, confesso que agora fiquei confuso rsrs . mais seguimos tentando ajudar. 

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos
4 minutos atrás, Weverton Todeschini disse:

@Thiago Anselmo não sugeri fazer o pivô, pelo que entendi do problema até agora, no excel vc tem uma coluna para demostrar tipos de analises diferentes, o que eu sugeri foi destrinchar 1 coluna, por exemplo nota(7) em 5 novas colunas cada uma com seu respectivo tipo de dados. adicionando colunas personalizadas fazendo a checagem de cada unidade de medida, conforme o exemplo. mais se ainda não for isso, talvez seja melhor postar o pbix com o exemplo final de como teria de ficar seus visuais, confesso que agora fiquei confuso rsrs . mais seguimos tentando ajudar. 

@Weverton Todeschini

Realmente está um pouco confuso mesmo, e meu pbix tão confuso quanto eu pois não estou conseguindo visualizar uma solução.

O que preciso desenvolver basicamente ao final de realizar a tratativa dos dados é uma visão como a disponibilizada abaixo.

Onde:

Meta 10 é referente ao campo Nota 10.

Mês atual é o Realizado no mês atual.

E acumulado o Realizado ao longo dos meses do projeto até o mês atual.

image.png.ae5825f2fddfb5584b8b6b6f6a1ea666.png

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

Boa Tarde, @Thiago Anselmo!

 

Tem até que horas para entregar o resultado?

Numa olhada rápida, o que indicaria.

Até as horas, na verdade, são uma forma de ver valores decimais.

Desta forma, criaria uma coluna convertendo a parte das horas em decimal e, no relatório, configuraria para apresentar o número formatado para hora, percentual, número inteiro, etc. em conformidade com a unidade de medida.

Não tive tempo de ler todos os comentários dos colegas acima, mas vendo que ainda não resolveu e dada a urgência, resolvi sugestionar a presente ideia para sua avaliação.

Vi, ainda, que no seu arquivo Excel, existem as linhas de 573 à 582 sem informação da Unidade de Medida.

Por enquanto, espero ter dado uma luz.

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

Olhei aqui e o maior problema é que sua base é muito mal prenchida, é hora em local que era para ser percentual, casa decimais como percentual, etc.

É impossível o Power Query prever todas as besteiras que o usuário pode fazer.

Você pode usar o código abaixo para converter a hora:

if Text.Contains(Text.From([Nota 7]), ":") then (Number.From([Nota 7]) * 24) - 24
else [Nota 7], type number)

 

Tem que aplicar ele para as colunas: Nota 7, Nota 10, Nota 15, Meta. Realizado e Realizado Acumulado.

 

Assim, você consegue com o SWITCH tratar os dados e gerar um resultado similar ao que quer:

image.png.0d2d63cdc2710fe2da1d34d539368095.png

 

image.png.231ff548cc24a007be5cb57eae7943a2.png

 

Acredito que com a técnica acima, resolva o seu problema, mas se a entrada de dados não for feita de modo adequado, de nada adiantará.
Observe ainda, que você tem mais de uma linha para determinado indicador, de modo que terá que ou criar um índice ou algo similar para ter uma equivalência de uma única linha para cada conjunto.

  • Like 1
Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos

@Vitor Peralva bom dia.

Obrigado por ajudar.

Você chegou no resultado demonstrado apenas aplicando o código na coluna para formato da hora? Fez alguma tratativa para os demais tipos de dados?

Tentei aplicar por aqui mas não tive sucesso. Se puder me auxiliar agradeço bastante, já estou há algumas semanas apanhando desse projeto.

 

 

 

Link para o comentário
Compartilhar em outros sites

  • 0
  • Alunos
Em 20/07/2022 em 02:08, Vitor Peralva disse:

Sim, apliquei para as colunas que listei, mas não usei todas no visual.

Segue PBIX para análise.

Dúvida - Thiago Anselmo.pbix 3 MB · 1 download

@Vitor Peralva, bom dia.

 

Mais uma vez, obrigado pelo apoio.

Irei analisar o pbix e tentar aplicar seus conceitos para quem sabe finalmente conseguir realizar essa entrega.

Grato.

Att,

Thiago Anselmo

Link para o comentário
Compartilhar em outros sites

Faça login para comentar

Você vai ser capaz de deixar um comentário após fazer o login



Entrar Agora
×
×
  • Criar Novo...