Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sangeethrayarot
Frequent Visitor

Dynamic Web Api Refreshing

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 

 

 

 

 

 

 

 

 

 



 

3 REPLIES 3
lbendlin
Super User
Super User

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.

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. 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors