RSS in Power BI

[Analitika]

Hello,

I would like to ask how to use RSS link in order to retrieve data. I used this https://nasdaqbaltic.com/statistics/en/news?rss=1&num=100&issuer= but it contains just 100 items per page, my goal is to obtain more data like per this year 2022, how I can achieve this. I will use link in Power BI.

[v-chenwuz-msft]

Hi @Analitika ,

You can create a query to get the result you want depend one page, and then change the url in the code to dynamic value and this query will change to a function.

After this, create a new table which contains all the value( page number ) you want. Add this table an Invoke Custom Funtion column via the function you just created.

You can refer this code for the query with all the page:

function:

let
    webresult = (num_1)  =>
    let
        Source = Xml.Tables(Web.Contents("https://nasdaqbaltic.com/statistics/en/news?rss="& Number.ToText(num_1) &"num=100&issuer=")),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:version", Int64.Type}}),
        channel = #"Changed Type"{0}[channel],
        #"Changed Type1" = Table.TransformColumnTypes(channel,{{"title", type text}, {"description", type text}, {"link", type text}, {"language", type text}, {"lastBuildDate", type datetimezone}}),
        item = #"Changed Type1"{0}[item],
        #"Changed Type2" = Table.TransformColumnTypes(item,{{"title", type text}, {"link", type text}, {"pubDate", type datetimezone}, {"issuer", type text}}),
        #"Expanded guid" = Table.ExpandTableColumn(#"Changed Type2", "guid", {"Element:Text", "Attribute:isPermaLink"}, {"guid.Element:Text", "guid.Attribute:isPermaLink"})
    in
        #"Expanded guid"
in webresult

Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RSS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RSS", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "re", each function([RSS])),
    #"Expanded re" = Table.ExpandTableColumn(#"Invoked Custom Function", "re", {"title", "link", "pubDate", "guid.Element:Text", "guid.Attribute:isPermaLink", "issuer"}, {"re.title", "re.link", "re.pubDate", "re.guid.Element:Text", "re.guid.Attribute:isPermaLink", "re.issuer"})
in
    #"Expanded re"

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Analitika]

Hello, I checked table but I see the limit of data. It is just two May days. My goal to obtain data from 2022 January to May.

[v-chenwuz-msft]

Hi @Analitika ,

You should consult the api usage guide of the site and use the function web.contents() of the power query to simulate the request. and get the data.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.