Forum Discussion
Unable to refresh Dynamic Data Sources
We are building a Power BI report and need the biographies of Congressional officials. However, because this is a dynamic data source, we cannot schedule refreshes in the Power BI service. M query is below. Any ideas?
let
Source = Xml.Tables(Web.Contents("https://www.senate.gov/legislative/LIS_MEMBER/cvc_member_data.xml")),
Table1 = Source{1}[Table],
#"Removed Columns" = Table.RemoveColumns(Table1,{"name", "party", "state", "homeTown", "stateRank", "office", "committees", "Attribute:lis_member_id", "leadership_position"}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns", "Text Before Delimiter", each Text.BeforeDelimiter([bioguideId], "0"), type text),
#"Merged Columns" = Table.CombineColumns(#"Inserted Text Before Delimiter",{"Text Before Delimiter", "bioguideId"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"bioguideId.1"),
#"Renamed Columns2" = Table.RenameColumns(#"Merged Columns",{{"bioguideId.1", "bioguideId"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns2", "fxBIO", each fxBIO([bioguideId])),
#"Expanded fxBIO" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxBIO", {"biography"}, {"biography"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded fxBIO",{{"biography", "Bio"}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"bioguideId", each Text.AfterDelimiter(_, "/"), type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"bioguideId", "bioguideID"}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Renamed Columns1", {"Bio"}),
#"Added Custom" = Table.AddColumn(#"Removed Errors", "BioSource", each "Source: http://bioguide.congress.gov/")
in
#"Added Custom"
There you go. Always try to keep best web.contents best practices made by Chris Webb. You can check an example here:
https://blog.ladataweb.com.ar/post/630597294839955456/powerquery-buena-práctica-para-un-web-request
If you change your function like this, it should recognize the data source. Be sure to make all data sources with the same privacy levels (it's recommended to use organizational)
(PageStart as text) => let Source = Xml.Tables(Web.Contents("https://bioguideretro.congress.gov/", [RelativePath= "Static_Files/data/"&PageStart&".xml"])) in SourceI hope that helps.
5 Replies
Hi. I don't think there is a problem with that xml. That should work pretty good. Can you show us the function fxBIO() ? that one is the responsable for the issue.
Let's see if we can find a solution together
This is the function. We used it to make the text look better in the report.
(PageStart as text) => let Source = Xml.Tables(Web.Contents("https://bioguideretro.congress.gov/Static_Files/data/"&PageStart&".xml")) in SourceThis is the error I get in the service. I have narrowed the refresh problems down to the XML queries. I really do appreciate your help!!
You can't schedule refresh for this dataset because the following data sources currently don't support refresh: Data source for Query1
There you go. Always try to keep best web.contents best practices made by Chris Webb. You can check an example here:
https://blog.ladataweb.com.ar/post/630597294839955456/powerquery-buena-práctica-para-un-web-request
If you change your function like this, it should recognize the data source. Be sure to make all data sources with the same privacy levels (it's recommended to use organizational)
(PageStart as text) => let Source = Xml.Tables(Web.Contents("https://bioguideretro.congress.gov/", [RelativePath= "Static_Files/data/"&PageStart&".xml"])) in SourceI hope that helps.