Forum Discussion
Unable to Refresh a Semantic Model withe a Web Source
Hi, Community!
I have a well-working semantic model with 2 sources: synapse and company sharepoint, that I am able to refresh without issues.
When I have added a web source to be able to get the correct offset time Time Zone & Clock Changes in Owensboro, Kentucky, USA (timeanddate.com), I get issue with the dataset refresh and see the following differences under the settings:
- The gateway connection is on and greyed out, not allowing me to turn it off as per our routines
- It requests the personal gateway, although, as per my understanding, we do not need any gateway for a web source
- The data source credentials are greyed out and I cannot edit them.
It works well to upload the new data from the web to the report in the PBI Desktop / Power Query.
I have the following permissions for the sources in the PBI Service connections and PBI Desktop:
Synapse (working well in the old version without the web sources)
Company SharePoint (working well in the old version without the web sources)
For the new web source I have tested the following variants, returning the same issue as in the 1st screenshot
I was not able to set the Oath2 on the cloud connection or Organizational account on the data source in the deskop
Our Hosting says that the source does not need to be whitelisted.
Would be grateful for any possible solutions here ⭐
Just re tested your own M code and it is working fine
What you could is start over : Delete the Query from Power Query and make sure to delete the source from here :
Save you report and reopen your Power Query and Get Data/Blank Query, in Query Editor Copy/Paste you M Code you just shared with me. Rename your Query 1 and republish your report.
5 Replies
Thanx for the hack, aj1973 !
I changed the M code as you said, put the anonymous to the data source in the query and on the web connection.In my case the data source credentials are totally greyed-out. That's not how it is for you.
And it gets greyed-out only if I add this web source. When I remove it, it works as usual and I can edit the connection.
Unfortunately, still the same issue.
What do you have on your data source connection, anonymous as well?
My M code is here:
let
Source = Web.Contents("https://www.timeanddate.com/time/zone/usa/owensboro"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.table.table\-\-left.table\-\-inner-borders-rows > * > TR > :nth-child(1)"}, {"Column2", "TABLE.table.table\-\-left.table\-\-inner-borders-rows > * > TR > :nth-child(2)"}}, [RowSelector="TABLE.table.table\-\-left.table\-\-inner-borders-rows > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "Current Offset:")),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Filtered Rows", "Current Offset", each Text.BetweenDelimiters([Column2], " ", " "), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Between Delimiters",{{"Current Offset", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Owensboro", each DateTimeZone.SwitchZone(DateTimeZone.LocalNow(), [Current Offset])),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column2", "TimeZoneOwe"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Column1", "Current Offset", "Owensboro", "TimeZoneOwe"}),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Reordered Columns", {{"Owensboro", type text}}, "sv-SE"), "Owensboro", Splitter.SplitTextByPositions({0, 6}, true), {"Owensboro.1", "Owensboro.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Owensboro.1", type datetime}, {"Owensboro.2", type duration}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type2", {{"Owensboro.1", type text}}, "sv-SE"), "Owensboro.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Owensboro.1.1", "Owensboro.1.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Owensboro.1.1", type date}, {"Owensboro.1.2", type time}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Owensboro.1.1", "Refresh Date_OWE"}, {"Owensboro.1.2", "Refresh Time_Owe"}})
in
#"Renamed Columns1"Just re tested your own M code and it is working fine
What you could is start over : Delete the Query from Power Query and make sure to delete the source from here :
Save you report and reopen your Power Query and Get Data/Blank Query, in Query Editor Copy/Paste you M Code you just shared with me. Rename your Query 1 and republish your report.
