d-portal data in Microsoft Power Bi

[HDSass]

Is it possible to automate access to COVID-19 d-portal data in MS Power Bi?

For example I'm hoping to automate access to the CSV data sheet for Afghanistan at: https://d-portal.org/ctrack.html?country_code=AF&aids=COVID-19#view=list_activities. Instead of downloading the individual CSV sheet, is there a way to import data from a URL to view in Power Bi? Here the idea is also that the dashboard I'm building in Power Bi can automate access to the data and remain up to date with the data on d-portal.

[matmaxgeds]

In case it helps, you could also try using the json data accessible from the datastore e.g.: https://iatidatastore.iatistandard.org/search/activity?q=recipient_country_code:(AF) AND (title_narrative:"covid" OR description_narrative:"covid" OR iati_identifier:"covid")&wt=json&rows=2500 - see also: https://iatidatastore.iatistandard.org/querybuilder, keen to hear how you get on as I have similar sounding needs in Palestine and Somalia.

[amy-silcock]

@xriss and @notshi what are your thoughts on this one?

[notshi]

Hi @HDSass @amy-silcock

We don't use MS Power Bi so can't really comment on that part of the question.

However, we do agree that this is something the datastore should provide, if it doesn't already; ie. as per @matmaxgeds suggestion.

We do have dquery that allows for complex queries but we'll need some time to think about the best way of explaining how this works.

For example, this is what the COVID-19 query looks like in dqueryOR(%0A%09root='/iati-activities/iati-activity/title/narrative'%20AND%0A%09to_tsvector('simple',%20xson-%3E%3E'')%20@@%20to_tsquery('simple','COVID-19')%0A)OR(%0A%09root='/iati-activities/iati-activity/description/narrative'%20AND%0A%09to_tsvector('simple',%20xson-%3E%3E'')%20@@%20to_tsquery('simple','COVID-19')%0A)OR(%0A%09root='/iati-activities/iati-activity/transaction/description/narrative'%20AND%0A%09to_tsvector('simple',%20xson-%3E%3E'')%20@@%20to_tsquery('simple','COVID-19')%0A)%0A)%0A%0A). We will need to limit it to Afghanistan for the list you require. In order to get the csv link and not a download, we had to make adjustments on the server to accommodate this new format.

Ultimately, it would be great to have some development time to make dquery friendlier. But until then, this should hopefully be the automated csv link you requiredOR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Ftitle%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Fdescription%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Ftransaction%2Fdescription%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)%0A%0A)%0A)%0A%0A).

There's a bit you can change in the url to get the different formats (csv, json, xml). Replacing ?form=csv&sql= with # will send you to the dquery page to see the query in question.

Updated the link, thanks Amy!

[amy-silcock]

Thanks @notshi. I agree this is something that should be done via the datastore.

For now, it would need multiple queries to get the same data as the d-query search provides. Which can be off putting. We're working on it.

@HDSass if you remove the github.com/devinit/D-Portal/issues/ from the link it should then work :)

[notshi]

Hi @HDSass - did the link work for you? Let us know if you need further help.

Otherwise, could you please close this issue, thank you.

[HDSass]

Thanks everyone for your replies and guidance. While on our end we didn't find a way to have Power Bi recognize tables on d-portal - as is the case with other websites where with a URL Power Bi can pick out tables - we're looking into the options you've suggested through d-query. I'll close this for now and come back if we have any further updates or questions.

[notshi]

Hi @HDSass, thanks for letting me know.

I downloaded Power Bi Desktop earlier and used these following steps to get it to recognise tables from d-portal.

1. Go to File > Get data > Web
2. Paste this linkOR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Ftitle%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Fdescription%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Ftransaction%2Fdescription%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)%0A%0A)%0A)%0A%0A) and click OK
3. Click Load

The tables then loaded on Power Bi for me using these steps.

Here is an example of a table using the above method:

https://app.powerbi.com/view?r=eyJrIjoiMTVkZjhhMWMtYTliMS00ZjQ4LTliY2QtMWRmNTg0ZjJhMGUxIiwidCI6IjQ2MjI3OWM0LWZjODMtNGRlNi1hODgxLTkwMTFlNWI4YTlmYSJ9

[HDSass]

Hi @notshi,

Many thanks for the solution!!

It works on my end in Power Bi. The solution you shared links to Total Projects for Afghanistan ( https://d-portal.org/ctrack.html?country_code=AF&aids=COVID-19#view=list_activities ).

Again using the example of Afghanistan, I'd like to ask if you are able to link Power Bi to the data in the screenshot below? It's really this data that I'm after. And similarly for the data in the second screenshot.

[image: image.png]

[image: image.png]

Many thanks for your time and help.

Regards, Damien.

On Wed, 23 Sep 2020 at 09:38, shi notifications@github.com wrote:

Hi @HDSass https://github.com/HDSass, thanks for letting me know.

I downloaded Power Bi Desktop earlier and used these following steps to get it to recognise tables from d-portal.

1. Go to File > Get data > Web
2. Paste this link http://d-portal.org/dquery?form=csv&sql=%0ASELECT%20*%20from%20act%20join%20country%20on%20act.aid%3Dcountry.aid%20%20WHERE%20country_code%3D%27AF%27%20AND%20act.aid%20in%20(%0ASELECT%20DISTINCT%20aid%20FROM%20xson%20WHERE%0A(%0A%0A(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Fhumanitarian-scope%27%20AND%0A%09xson-%3E%3E%27%40type%27%3D%271%27%20AND%0A%09xson-%3E%3E%27%40vocabulary%27%3D%271-2%27%20AND%0A%09xson-%3E%3E%27%40code%27%3D%27EP-2020-000012-001%27%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Fhumanitarian-scope%27%20AND%0A%09xson-%3E%3E%27%40type%27%3D%272%27%20AND%0A%09xson-%3E%3E%27%40vocabulary%27%3D%272-1%27%20AND%0A%09xson-%3E%3E%27%40code%27%3D%27HCOVD20%27%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Ftag%27%20AND%0A%09xson-%3E%3E%27%40vocabulary%27%3D%2799%27%20AND%0A%09xson-%3E%3E%27%40vocabulary-uri%27%20IS%20NULL%20AND%0A%09UPPER(xson-%3E%3E%27%40code%27)%3D%27COVID-19%27%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Ftitle%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Fdescription%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)OR(%0A%09root%3D%27%2Fiati-activities%2Fiati-activity%2Ftransaction%2Fdescription%2Fnarrative%27%20AND%0A%09to_tsvector(%27simple%27%2C%20xson-%3E%3E%27%27)%20%40%40%20to_tsquery(%27simple%27%2C%27COVID-19%27)%0A)%0A%0A)%0A)%0A%0A and click OK
3. Click Load

The tables then loaded on Power Bi for me using these steps.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/devinit/D-Portal/issues/589#issuecomment-697369874, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQPUYO5XTDY6Z7CC4G3CNVLSHH25FANCNFSM4PTPKBDA .

[amy-silcock]

Hi @HDSass how are you getting on with this work?

I know the issue has been closed but it would be great to hear an update.

Thanks,

Amy

[notshi]

Hi @amy-silcock is this still an issue? I thought this was closed as @HDSass has received help via emails.

[amy-silcock]

Hi @notshi, not an ongoing issue.

Just wondering if @HDSass was able to share any progress. Happy for this to be closed :)