Vários tipos de dados em uma coluna excel

[Thiago Anselmo]

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:
 

[Vitor Peralva]

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

[Alexandre H. Moraes]

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.

[Thiago Anselmo]

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...

[joaomarraes]

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.

[Thiago Anselmo]

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

[Weverton Todeschini]

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. 

Por exemplo, dessa forma. 

 

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 

 

[Weverton Todeschini]

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.

 

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

[Thiago Anselmo]

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.

 

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

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.

 

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.

 

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"

[Weverton Todeschini]

@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. 

[Thiago Anselmo]

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.

[Weverton Todeschini]

Certo. Agora começar clarear um pouco rs. A noite consigo voltar a dar uma olhada. e pensar em como extrair. caso alguém não tenha conseguido ajudar ainda te retorno aqui. Abs.

[Thiago Anselmo]

@Weverton Todeschini

Muito obrigado pela ajuda meu caro.

[Vitor Peralva]

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.

[Vitor Peralva]

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:

 

 

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.

[Nelson Kobayashi]

38 minutos atrás, Vitor Peralva disse:

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

Se a Inteligência Artificial começar a conseguir prever todas as besteiras que o usuário pode fazer, corre que é a SKYNET chegando para nos exterminar .... kkkk

[Thiago Anselmo]

@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.

 

 

 

[Thiago Anselmo]

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