Unable to refresh Dynamic Data Sources

[KelliKnitsAlot]

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"

[ibarrau · Accepted solution]

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
    Source

I hope that helps.

[ibarrau]

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

[KelliKnitsAlot]

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
    Source

This 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

[KelliKnitsAlot]

Thanks!!  It appears to be working. I am going to schedule a refresh to run in about 30 mins to make sure the auto refresh works and will reply back to the thread. Thank you so much! 

[KelliKnitsAlot]

Scheduled refresh worked perfectly! Thanks!!