Dynamic Web Api Refreshing

sangeethrayarot — Wed, 15 Mar 2023 14:14:20 GMT

Hi Guys,

I have an api which fetches data from social media platforms. Using this api, i created a query that fetches last 10 days of data from the social media and writes it to a postgresql database. After this step the same query loads the data from the same database.

But i have an issue with the refresh. When i refresh it in power query ie "Refresh All", the query fetch the data and update the database as expected. But when i refresh it from power bi ie "Refresh" it is not fetching the latest data.

Anyone have an idea about this. Is this because the start date of the api is dynamic?

let
date_from = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -10), "yyyy-MM-dd"),
url = "https://connectors.abc.com/snapchat?api_key=sampleapikey&date_from=" & Text.From(date_from) & "&date_to=2200-12-31&fields=account_name,ad_name,date,impressions,spend,swipes,totalcost&_renderer=powerbi",
Source = Json.Document(Web.Contents(url)),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"account_name","ad_name", "date", "impressions", "spend", "swipes", "totalcost"}, {"Account","data.ad_name", "data.date", "data.impressions", "data.spend", "data.swipes", "data.totalcost"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded data1", each ([data.ad_name] <> null)),
#"Added Social Media" = Table.AddColumn(#"Filtered Rows", "Social Media", each "Snapchat"),
#"Added Id" = Table.AddColumn(#"Added Social Media", "Ad Id", each [data.ad_name]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Id",{{"data.ad_name", type text}, {"data.date", type date}, {"data.impressions", Int64.Type}, {"data.spend", Int64.Type}, {"data.swipes", Int64.Type}, {"data.totalcost", type number}, {"Social Media", type text}, {"Ad Id", type text}, {"Account", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"data.spend"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"data.ad_name", "Ad Name"}, {"data.date", "Date"}, {"data.impressions", "impressions"}, {"data.swipes", "clicks"}, {"data.totalcost", "spend"}}),
#"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Ad Name", "ad_name"}, {"Social Media", "social_media"}, {"Ad Id", "ad_id"}, {"Date", "date"}, {"Account", "account"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"date", type text}}),
#"Writing to DB" = R.Execute("# load the required libraries#(lf)library(dplyr)#(lf)library(DBI)#(lf)#(lf)# make a copy of original data#(lf)original_table <- dataset#(lf)#(lf)# sample data for export to PostgreSQL#(lf)sample_table <- sample_n(dataset, 10)#(lf)#(lf)# estabish connection to selected Postgre database#(lf)con <- dbConnect(RPostgres::Postgres(), dbname=""marketing_dashboards"", host=""myserver"", port=5432, user=""postgresql"", password=""password"", options=""-c search_path=social_media"")#(lf)#(lf)dbGetQuery (con, ""DELETE FROM social_media.snapchat WHERE date > (current_date - interval '11 days')"")#(lf)#(lf)dbAppendTable (con, ""snapchat"", original_table)#(lf)#(lf)dbDisconnect(con)",[dataset=#"Changed Type1"]),
#"Loading From DB" = Odbc.Query("dsn=Postgres", "select * from social_media.snapchat"),
#"Renamed Columns2" = Table.RenameColumns(#"Loading From DB",{{"account", "Account"}, {"ad_name", "Ad Name"}, {"date", "Date"}, {"impressions", "Impressions"}, {"clicks", "Clicks"}, {"spend", "Spend"}, {"social_media", "Social Media"}, {"ad_id", "Ad Id"}})
in
#"Renamed Columns2"

amitchandak

Re: Dynamic Web Api Refreshing

lbendlin — Fri, 17 Mar 2023 02:31:43 GMT

There is NO GUARANTEE that Power Query will execute your code exactly once. DO NOT attempt to abuse Power Query to write anything into a data source.

Chris Webb's BI Blog: Why Is Power BI Running My SQL Query Twice? (crossjoin.co.uk)

Find a different way to run your process.

Re: Dynamic Web Api Refreshing

sangeethrayarot — Mon, 20 Mar 2023 06:56:48 GMT

The query is running as expected. Except for the refresh. While refreshing, the dynamic date range is not changing. I dont think there is anything abusive here.

Re: Dynamic Web Api Refreshing

lbendlin — Mon, 20 Mar 2023 16:28:12 GMT

"abuse" in the sense that you are using Power Query for something it was never designed for. Use a proper ETL tool.

topic Dynamic Web Api Refreshing in Power Query

Dynamic Web Api Refreshing

Re: Dynamic Web Api Refreshing

Re: Dynamic Web Api Refreshing

Re: Dynamic Web Api Refreshing