Como driblar o "Tipo Alterado" para não perder a consulta nativa SQL

[Tiago Serber Tavares Dutra]

Olá Incomparáveis, tudo bem?

Eu estou usando um banco de dados DataLake para conectar na fonte de dados. Não digitei código na consulta avançada(código SQL) e preferi trabalhar usando a consulta nativa SQL do power query. O código que vou colocar abaixo está funcionando e eu tratei/carreguei os dados do jeito que eu queria e precisava, PORÉM,  a consulta nativa se quebrou e não ficou nada performático, ou seja, está causando certa lentidão para carregar os dados...

Eu tentei substituir as linhas de código do "Tipo alterado" (dentro do editor avançado do Power Query) pelas linhas que destaquei em amarelo abaixo, porém apesar dos códigos terem funcionado perfeitamente, continua "quebrando" a consulta nativa SQL mesmo assim... Alguém consegue me dar uma dica de como posso alterar o tipo de dado de uma coluna (via power query/linguagem M) sem que se perca/quebre a consulta nativa SQL?

Até o passo #"Últimos caracteres inseridos", a consulta nativa ainda está viva, porém depois da tentativa de alterar o tipo do dado "texto" para "inteiro" a consulta nativa morre!😭

 

>>> início do código >>>>

let

    Fonte = Sql.Database("datalake1.database.windows.com", "TabLog"),

    log_Devolucoes = Fonte{[Schema="log",Item="Devolucoes"]}[Data],

    #"Linhas Filtradas" = Table.SelectRows(log_Devolucoes, each [Data Doc Devolucao] >= #date(2023, 1, 1)),

    #"Outras Colunas Removidas" = Table.SelectColumns(#"Linhas Filtradas",{"Centro", "Data Doc Devolucao", "Quantidde Devolvida", "Peso Devolvido", "Periodo Referencia"}),

    #"Linhas Filtradas2" = Table.SelectRows(#"Outras Colunas Removidas", each [Periodo Referencia] <> null and [Periodo Referencia] <> ""),

    #"Últimos caracteres inseridos" = Table.AddColumn(#"Linhas Filtradas2", "MêsPeriodoRef", each Text.End([Periodo Referencia], 1), Int64.Type),

    #"Alt-Ult-Caract-Ins" = Table.AddColumn(#"Últimos caracteres inseridos", "MêsPeriodoRef2", each Number.FromText([MêsPeriodoRef]),Int64.Type),

    #"Dia Inserido" = Table.AddColumn(#"Alt-Ult-Caract-Ins", "DiaDocDev", each Date.Day([Data Doc Devolucao]), Int64.Type),

    #"Mês Inserido" = Table.AddColumn(#"Dia Inserido", "Mês", each Date.Month([Data Doc Devolucao]), Int64.Type),

    #"Ano Inserido" = Table.AddColumn(#"Mês Inserido", "Ano", each Date.Year([Data Doc Devolucao]), Int64.Type),

    #"Personalização Adicionada1" = Table.AddColumn(#"Ano Inserido", "Teste-mes", each if [Mês] - [MêsPeriodoRef2] = 1 and [DiaDocDev] <= 15 then "Trocar Mês" else "Manter Mês"),

    #"Colunas Mescladas1" = Table.CombineColumns(Table.TransformColumnTypes(#"Personalização Adicionada1", {{"DiaDocDev", type text}, {"MêsPeriodoRef", type text}, {"Ano", type text}}, "pt-BR"),{"DiaDocDev", "MêsPeriodoRef", "Ano"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"DataTemp"),

    #"Alt-Tip-Col-Mesc" = Table.AddColumn(#"Colunas Mescladas1", "DataTemp2", each Date.FromText([DataTemp]), type date),

    #"Coluna Condicional Adicionada" = Table.AddColumn(#"Alt-Tip-Col-Mesc", "Data Devolução", each if [#"Teste-mes"] = "Trocar Mês" then [DataTemp2] else [Data Doc Devolucao]),

    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Coluna Condicional Adicionada",{"Centro", "Quantidde Devolvida", "Peso Devolvido", "Data Devolução"}),

    #"Linhas Agrupadas" = Table.Group(#"Outras Colunas Removidas1", {"Centro", "Data Devolução"}, {{"QTD_CX_Devolução", each List.Sum([Quantidde Devolvida]), type nullable number}, {"PESO_Devolução", each List.Sum([Peso Devolvido]), type nullable number}}),

    #"Linhas Filtradas1" = Table.SelectRows(#"Linhas Agrupadas", each [Data Devolução] >= #date(2023, 1, 1) and [Data Devolução] <= #date(2023, 4, 30))

in

    #"Linhas Filtradas1"

 

>>> Fim do código >>>>

 

Sei que muitos vão dizer que é mais fácil fazer o código todo em SQL e já trazer a base tratada pra dentro do Power BI, porém além de não terem aprovado a instalação do SQL Server Management Studio na minha máquina, eu ainda estou engatinhando na lógica/códigos SQL e ainda não consigo desenvolver o código... Além disso, estou com um tempo curtíssimo para entregar este projeto(dashboard).

Peço desculpas desde já por não ter anexado o PBIX + Fontes de dados, mas preciso montar ainda uma base 100% fictícia antes de anexar aqui. Se puderem me ajudar com o que já coloquei aqui de informação ficarei muito grato. 

Editado Julho 7, 2023 por Tiago Serber Tavares Dutra

[Joao Raulino]

Suave @Tiago Serber Tavares Dutra?

Conseguiu resolver o teu caso? De acordo com alguns testes feitos pelo pessoal da comunidade qualquer transformação de TIpo Texto vai causar a quebra da consulta nativa:

Uma possível solução seria alterar as funções Number.FromText() e Date.FromText() para as versões Number.From() e Date.From(), mas o resultado vai depender muito de como o Power Query interpreta o tipo de cada coluna então tem que testar.

Não dando certo, uma outra alternativa seria criando uma coluna copia para só então transformar o tipo dela com a função Table.TransformColumns() ou então fazendo a conversão na própria instrução SQL através da caixinha que o @champanjonata mencionou.

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

Editado Dezembro 30, 2023 por Joao Raulino

[Renato Carlos Rossato]

Olá @Tiago Serber Tavares Dutra, tenta não realizar essa etapas de tipo alterado e deixar essas colunas de mês e ano para serem criadas na tabela calendário se possível.

Qualquer coisa chama no WhatsApp que te ajudo.

[Tiago Serber Tavares Dutra]

Olá Renato. Antes de mais nada, muito obrigado por ter retornado!

Rapaz, infelizmente não tem como eu deixar de fazer esta etapa pois:

1 - estou trabalhando com uma base com mais de 10 milhões de linhas, então se eu quero melhorar a performance, eu só posso carregar a tabela já sumarizada(resumida), e pra isso eu tenho que fazer conforme vou explicar abaixo. Então Levando a base inteira e depois fazendo os cálculos, pioraria a performance mais lento do que já está...

2 - eu preciso testar o período de referência das devoluções para considerar esta devolução dentro do mês correto, todavia eu não consigo fazer os cálculos com tipos text e depois não consigo transformar a data nova (que eu vou concatenar) em tipo "date"(pois qdo concatena fica como texto), então não dá pra deixar de transformar os tipos de dados ao longo antes de carregar...

Editado Julho 8, 2023 por Tiago Serber Tavares Dutra

[champanjonata]

Em 08/07/2023 em 12:33, Tiago Serber Tavares Dutra disse:

Olá Renato. Antes de mais nada, muito obrigado por ter retornado!

Rapaz, infelizmente não tem como eu deixar de fazer esta etapa pois:

1 - estou trabalhando com uma base com mais de 10 milhões de linhas, então se eu quero melhorar a performance, eu só posso carregar a tabela já sumarizada(resumida), e pra isso eu tenho que fazer conforme vou explicar abaixo. Então Levando a base inteira e depois fazendo os cálculos, pioraria a performance mais lento do que já está...

2 - eu preciso testar o período de referência das devoluções para considerar esta devolução dentro do mês correto, todavia eu não consigo fazer os cálculos com tipos text e depois não consigo transformar a data nova (que eu vou concatenar) em tipo "date"(pois qdo concatena fica como texto), então não dá pra deixar de transformar os tipos de dados ao longo antes de carregar...

Você não precisa necessariamente do SQL SERVER Management Studio para usar queries:

[Julian Ferreira Salcedo]

Olá @Tiago Serber Tavares Dutra tudo bem? 😃

Esperamos que você tenha encontrado a solução que tanto desejava!!

Caso alguma resposta tenha ajudado, você pode marcá-la como Solução ✅. Com isso o tópico poderá ser fechado! 

Maas...

Pode acontecer de você receber uma resposta que não é 100% do que você esperava. Mesmo assim, se ela o ajudou de alguma forma para que você mesmo conseguisse chegar à solução, conte como você fez isso e marque como a melhor resposta. 😉

 

Obrigado por tonar nossa comunidade #INCOMPARÁVEL!!

Att Julian Salcedo I Community Manager Xperiun

 

[Tiago Serber Tavares Dutra]

Em 30/12/2023 em 12:37, Joao Raulino disse:

Suave @Tiago Serber Tavares Dutra?

Conseguiu resolver o teu caso? De acordo com alguns testes feitos pelo pessoal da comunidade qualquer transformação de TIpo Texto vai causar a quebra da consulta nativa:

Uma possível solução seria alterar as funções Number.FromText() e Date.FromText() para as versões Number.From() e Date.From(), mas o resultado vai depender muito de como o Power Query interpreta o tipo de cada coluna então tem que testar.

Não dando certo, uma outra alternativa seria criando uma coluna copia para só então transformar o tipo dela com a função Table.TransformColumns() ou então fazendo a conversão na própria instrução SQL através da caixinha que o @champanjonata mencionou.

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

Obrigado João, ajudou muito.