Dataset Refresh on PowerBI Service not working

[AchalDesai]

Hi All,

I am new to using PowerBI services and need some help with setting up an online Datset Refresh.

The report setup and background is explained below so it is better to understand where the gap/problem might be.

I have created an App on PowerApps that feeds into a SharePoint Online List.

Users can add and modify the records in this SharePoint List through this app.

I need to merge and transform the data from this SharePoint list to get the end result that is to be published in PowerBI.

The relationship diagram is below for reference.

-The table SP_LostPackagesDB is the direct source (Get Data) from the SharePoint Online List.

-The table Calender is a query directly created in PowerBI desktop that has dates starting from 01/01/2019 and then adds previous complete day with each passing day. (If refreshed today, 30/08/2021 will be added to the table)

-The table CurrencyExchange is a query created by invoking a cutom function on the Calander table that gets web content (currency exchange rates) from a website for all the dates in the Calander table.

The table PBI_LostPackagesDB is the final query that I use in the reports. It is created by merging the SP_LostPackagesDB and the CurrencyExchange table with a join on the Date and Currency columns.

All these tables refresh correctly in PowerBi Desktop. But it does not refresh directly on the PowerBI Online Service. Every day I need to refresh the queries on PowerBi Desktop and repblish the reports to update the dataset.

Solution Needed:

1. I want help with setting up the dataset such that all tables and queries get refreshed automatically online instead of me having to publih them everyday.

2. Setup an incremental refresh on the CurrencyExchange table so that it gets the currency exchange rates for the previous day only and not run the custom function for all days since 01/01/2019. This so that the query loads faster and the currency rates for historical date will stay the same.

I hope you guys will be able to help me with this problem. 

[edhans]

You will need to provide more info. There is no reason they won't refresh in the service that I can see, so what error are you getting? Do you have a gateway set up? Have you read the article on the Incremental Refresh settings needed for your Currency table? You have a Date/Time field, so as long as that is a source that supports it (SQL for example, not Excel files or web pages), it should work.

We need more than "it isn't working" to assist.

[AchalDesai]

Hi @edhans,

Sorry if my explanation is not entirely undertandable.

I have the Dataset on a scheduled refresh every day at 08:00 AM.

When I log in at 09:00 AM, I see that the last refresh time on the dataset is somewhere around 08:15 AM, which tells me that the dataset was sucessfuly refreshed.

However, when I go into the reports and visuals, I should see the currency exchange rates for the previous day. (30/08/2021 if I am looking today). But what I see is the lastest date being 29/08/2021 which I had published from PBI Desktop.

Ideally, when I login tomorow, I should see the latest record added for the currency exchange for the date 31/08/2021.

As for the incremental refresh, I did setup the StartRange and EndRange parameters and the filter on the CurrencyExchange Query. Although, I'm not sure if it would work as the data in the query is actually being pulled by a function that is getting the web contents from a currency exchange website.

I am putting in my queries for each table. Maybe that would help figure out why the exchange rates for the previous day are not refreshing.

Calendar

let
    Source = {Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-Duration.Days(Date.From(DateTime.LocalNow())-#date(2019,1,1))))..Number.From(DateTime.Date(DateTime.LocalNow()))-1},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Date API Format", each Date.ToText(DateTime.Date([Date]), "yyyy-MM-dd"))
in
    #"Added Custom"

SP_LostPackagesDB

let
    Source = SharePoint.Tables("https://companydomain.sharepoint.com/sites/LostPackages", [Implementation="2.0", ViewMode="All"]),
    #"073db108-8a03-4bec-af67-70167ff7a0cf" = Source{[Id="073db108-8a03-4bec-af67-70167ff7a0cf"]}[Items],
    #"Expanded Created By" = Table.ExpandListColumn(#"073db108-8a03-4bec-af67-70167ff7a0cf", "Created By"),
    #"Expanded Created By1" = Table.ExpandRecordColumn(#"Expanded Created By", "Created By", {"title"}, {"Created By.title"}),
    #"Expanded Modified By" = Table.ExpandListColumn(#"Expanded Created By1", "Modified By"),
    #"Expanded Modified By1" = Table.ExpandRecordColumn(#"Expanded Modified By", "Modified By", {"title"}, {"Modified By.title"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Modified By1",{{"Last Ops Status Update", type date}, {"EDD Date", type date}, {"Modified", type date}, {"Created", type date}, {"Package added to Log", type date}, {"Last Tracking Date", type date}, {"Last Carrier Status Update", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Days From Last Ops Status Update", each if ([Last Ops Status Update] = null or [Operations Status]="Pending - New Claim") then Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Created]) else Duration.Days(DateTime.Date(DateTime.LocalNow())-[Last Ops Status Update])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Days from Last Carrier Status Update", each if [Carrier Status] = null or [Carrier Status] = "" or [Last Carrier Status Update]=null then Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Created]) else Duration.Days(DateTime.Date(DateTime.LocalNow())-[Last Carrier Status Update])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Brand Name] <> ""))
in
    #"Filtered Rows"

CurrencyExchange

let
    Source = Calendar,
    #"Invoked Custom Function" = Table.AddColumn(Source, "Currency", each fnGetExchangeRates([Date API Format])),
    #"Expanded Currency" = Table.ExpandTableColumn(#"Invoked Custom Function", "Currency", {"Currency", "Units per EUR", "EUR per unit"}, {"Currency.1", "Units per EUR", "EUR per unit"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Currency",{{"Currency.1", "Currency"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Date", "Date - Copy"),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Date - Copy", "Date/Time"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date/Time", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"Date/Time"] >= RangeStart and [#"Date/Time"] <= RangeEnd)
in
    #"Filtered Rows"

PBI_LostPackagesDB

let
    Source = Table.NestedJoin(SP_LostPackagesDB, {"Payment Currency", "Package added to Log"}, CurrencyExchange, {"Currency", "Date"}, "CurrencyExchange", JoinKind.LeftOuter),
    #"Expanded CurrencyExchange" = Table.ExpandTableColumn(Source, "CurrencyExchange", {"EUR per unit"}, {"EUR per unit"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded CurrencyExchange",{{"EUR per unit", "Exchange Rate"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Value in EUR", each Number.Round([Goods Value]*[Exchange Rate],3)),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{\\Lot of Column Names}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Created By.title", type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Value in EUR", type number},{"Days From Last Ops Status Update", type number},{"Days from Last Carrier Status Update", type number}})
in
    #"Changed Type"

fnGetExchangeRates

let
    Source = (Date as text) => let
        Source = Web.Contents("https://www.xe.com/currencytables/",[RelativePath = "?from=EUR&date=" & Date]),
        #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(1)"}, {"Column2", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(2)"}, {"Column3", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(3)"}, {"Column4", "TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR > :nth-child(4)"}}, [RowSelector="TABLE.currencytables__Table-xlq26m-3.jaGdii > * > TR"]),
        #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency", type text}, {"Name", type text}, {"Units per EUR", type number}, {"EUR per unit", type number}})
    in
        #"Changed Type"
in
    Source

[edhans]

Incremental Refresh won't work with a web page. It requires a relational database that Power Query can create a SQL statement to send back to the server for. No such facility for that on a web page. SQL Server, Access, Oracle, PostGRE SQL, etc will work. Web, CSV, SharePoint lists, Excel files, etc. will not.