I want to update my data without needing the Gateway installed on my machine

[madpimenta013]

Can anyone help me how I can make my dashboards update without needing a gateway installed on my machine? I want to make the update happen, without needing a dependency on my machine being on. Does anyone know how to help me how can I do this?

[aj1973]

Hi @madpimenta013 

The CSV file located in your machine is what triggering the need for a Gateway and for all the sources. If you want to get rid of the Gateway then you will need to upload the CSV file into a cloud service, like Sharepoint online or Onedrive for business

[otravers]

It all depends on your data sources. If you're just trying to load Excel or CSV files, then put them in SharePoint Online instead of a local drive and you won't need a data gateway.

[madpimenta013]

What about other data sources like API link or MySQL database?

[otravers]

REST API available on the web won't need a gateway. MySQL needs a gateway:

https://learn.microsoft.com/en-us/azure/mysql/flexible-server/connect-with-powerbi-desktop#connect-t...

[madpimenta013]

Strange, because in this dashboard I have 3 data sources: Google Analytics, API Link in JSON format and a CSV file. For the JSON link, when I go to set up auto-update it asks me to associate this datasource with a Gateway connection.

[otravers]

Can you share the M code for your API query?

[madpimenta013]

Of course. The source is set within a parameter with the link value:
https://seufgtshoje.agi.com.br/wp-json/agibank/v1/leads?token=zGn28noaXDAGwy6r9ckvAJeLi3BR8gG7&perPa...

The M query is as follows:

let
Fonte = Json.Document(Web.Contents(#"Tabela de Leads")),
#"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 Expandido" = Table.ExpandRecordColumn(#"Convertido para Tabela", "Column1", {"id", "nome", "telefone", "email", "saldo_simulado", "valor_contratado", "erro", "cpf", "link", "dia_aniversario", "mes_aniversario", "ano_aniversario", "created_at", "pagina_atual", "ultimo_acesso", "utm_campaign", "utm_source", "utm_medium", "utm_term", "utm_content"}, {"id", "nome", "telefone", "email", "saldo_simulado", "valor_contratado", "erro", "cpf", "link", "dia_aniversario", "mes_aniversario", "ano_aniversario", "created_at", "pagina_atual", "ultimo_acesso", "utm_campaign", "utm_source", "utm_medium", "utm_term", "utm_content"}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Column1 Expandido",{{"id", type text}, {"nome", type text}, {"telefone", type text}, {"email", type text}, {"saldo_simulado", type text}, {"valor_contratado", Currency.Type}, {"erro", type text}, {"cpf", type text}, {"link", type text}, {"dia_aniversario", Int64.Type}, {"mes_aniversario", Int64.Type}, {"ano_aniversario", Int64.Type}, {"created_at", type datetime}, {"pagina_atual", type text}, {"ultimo_acesso", type datetime}, {"utm_campaign", type text}, {"utm_source", type text}, {"utm_medium", type text}, {"utm_term", type text}, {"utm_content", type text}}),
#"Texto Inserido Após o Delimitador" = Table.AddColumn(#"Tipo Alterado", "Texto Após o Delimitador", each Text.AfterDelimiter(Text.From([created_at], "pt-BR"), " "), type text),
#"Colunas Reordenadas" = Table.ReorderColumns(#"Texto Inserido Após o Delimitador",{"id", "nome", "telefone", "email", "saldo_simulado", "valor_contratado", "erro", "cpf", "link", "dia_aniversario", "mes_aniversario", "ano_aniversario", "created_at", "Texto Após o Delimitador", "pagina_atual", "ultimo_acesso", "utm_campaign", "utm_source", "utm_medium", "utm_term", "utm_content"}),
#"Texto Extraído Antes do Delimitador" = Table.TransformColumns(#"Colunas Reordenadas", {{"created_at", each Text.BeforeDelimiter(Text.From(_, "pt-BR"), " "), type text}}),
#"Colunas Renomeadas" = Table.RenameColumns(#"Texto Extraído Antes do Delimitador",{{"created_at", "data_created_at"}}),
#"Texto Inserido Após o Delimitador1" = Table.AddColumn(#"Colunas Renomeadas", "Texto Após o Delimitador.1", each Text.AfterDelimiter(Text.From([ultimo_acesso], "pt-BR"), " "), type text),
#"Texto Extraído Antes do Delimitador1" = Table.TransformColumns(#"Texto Inserido Após o Delimitador1", {{"ultimo_acesso", each Text.BeforeDelimiter(Text.From(_, "pt-BR"), " "), type text}}),
#"Colunas Reordenadas1" = Table.ReorderColumns(#"Texto Extraído Antes do Delimitador1",{"id", "nome", "telefone", "email", "saldo_simulado", "valor_contratado", "erro", "cpf", "link", "dia_aniversario", "mes_aniversario", "ano_aniversario", "data_created_at", "Texto Após o Delimitador", "pagina_atual", "ultimo_acesso", "Texto Após o Delimitador.1", "utm_campaign", "utm_source", "utm_medium", "utm_term", "utm_content"}),
#"Colunas Renomeadas1" = Table.RenameColumns(#"Colunas Reordenadas1",{{"Texto Após o Delimitador", "hora_created_at"}, {"ultimo_acesso", "data_ultimo_acesso"}, {"Texto Após o Delimitador.1", "hora_ultimo_acesso"}}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Colunas Renomeadas1",{{"data_created_at", type date}, {"data_ultimo_acesso", type date}, {"hora_created_at", type time}, {"hora_ultimo_acesso", type time}}),
#"Colunas Removidas" = Table.RemoveColumns(#"Tipo Alterado1",{"link", "saldo_simulado"}),
#"Tipo Alterado2" = Table.TransformColumnTypes(#"Colunas Removidas",{{"id", Int64.Type}}),
#"Coluna Duplicada" = Table.DuplicateColumn(#"Tipo Alterado2", "data_created_at", "data_created_at - Copiar"),
#"Colunas Renomeadas2" = Table.RenameColumns(#"Coluna Duplicada",{{"data_created_at - Copiar", "DateKey"}}),
#"Tipo Alterado3" = Table.TransformColumnTypes(#"Colunas Renomeadas2",{{"DateKey", type datetime}}),
#"Linhas Filtradas" = Table.SelectRows(#"Tipo Alterado3", each [DateKey] >= RangeStart and [DateKey] < RangeEnd),
#"Erros Substituídos" = Table.ReplaceErrorValues(#"Linhas Filtradas", {{"dia_aniversario", 0}}),
#"Erros Substituídos1" = Table.ReplaceErrorValues(#"Erros Substituídos", {{"mes_aniversario", 0}}),
#"Erros Substituídos2" = Table.ReplaceErrorValues(#"Erros Substituídos1", {{"ano_aniversario", 0}})
in
#"Erros Substituídos2"

[otravers]

Is the "Tabela de Leads" query sourced from a local CSV? That would be the dependency that requires the entire thing to require a gateway.