Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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 ⭐
Solved! Go to Solution.
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi @Alisea_MI
When Get Data from Web use Ananymous, then in Query Editor delete "Browser" and leave it like this
in the service use Ananymous as well like this:
you should be good. I tested it and it's working.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Once again, thanx, @aj1973 !
I managed to solve it using your trick and in a new file - I think something got stuck in my old one. Now everything is working.
Hi @Alisea_MI
Can you please screenshot the M code the Query editor?
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook