Power Bi Service - Data Privacy Refresh

[RoskBI]

Hi community,

I was hoping for some help.

I have a dashboard which pulls together all the current tenders from Australian Government states through their individual websites.

However, the dashboard relies on websites and when using Power BI Service to refresh, it won't let me refresh the data and based on privacy issues (screenshot below).

In Power BI Desktop all the datasources privacy is set to "None" and I am unsure on what else could be causing the error.

[RoskBI]

@aj1973 One of the websites is here and publicly accessible. Please let me know if you think there is another way to get the data without hte Web.BrowserContents 

https://www.tenders.vic.gov.au/tender/search?keywords=&tenderCode=&tenderTitle=&tenderState=OPEN&ten... 

[aj1973]

In your desktop connect to the web as Ananymous, then in Power Query open the Editor and change this to

In the Service sign in this way

You should be good

[RoskBI]

I will give this a try this afternoon. Just to confirm will Web.Contents work with the "Add Table from Example" functionality?

[aj1973]

I don't know, I have to try it my self.

Give me the M Code please !

[RoskBI]

@aj1973 thank you so much for your help with this.

let
    Source = Web.BrowserContents("https://www.tenders.act.gov.au/tender/search?keywords=&tenderCode=&tenderTitle=&tenderState=OPEN&tenderType=&prequalificationCategory=&procurementCategory=&categories%5B0%5D.id=104789&categories%5B0%5D.percentage=100&issuingBusinessId=&awardedSupplier.id=&awardedSupplier.name=&openThisWeek=false&openingDateFrom=&openingDateTo=&ageRestriction=&closeThisWeek=false&closingDateFrom=&closingDateTo=&awardedThisWeek=false&awardedDateFrom=&awardedDateTo=&orderBy=&groupBy=NONE&page=&searchTitle="),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "B:nth-last-child(4)"}, {"Column2", "TD:nth-child(2) > .tablesaw-cell-content > DIV:nth-child(1):nth-last-child(1) > :nth-child(1)"}, {"Column3", ".tender-date > .tablesaw-cell-content"}, {"Column4", ".tablesaw-cell-content > DIV:nth-child(2) > UL[style*=""list-style-position\:initial""][style*=""list-style-image\:initial""][style*=""list-style-type\:none""]:nth-child(1):nth-last-child(1) > LI:nth-child(1):nth-last-child(1) > A:nth-child(1):nth-last-child(1)", each [Attributes][href]?}, {"Column5", "TD:nth-child(2) > .tablesaw-cell-content > DIV:nth-child(1):nth-last-child(1) > :nth-child(2)"}}, [RowSelector="[id*=""tenderRow259""]"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"Column1", Text.Clean, type text}, {"Column2", Text.Clean, type text}, {"Column3", Text.Clean, type text}, {"Column4", Text.Clean, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Cleaned Text",{{"Column1", "Tender ID"}, {"Column2", "Title"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Link", each "https://www.tenders.act.gov.au"&[Column4]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column4"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column3", Splitter.SplitTextByEachDelimiter({"Released"}, QuoteStyle.Csv, false), {"Column3.1", "Column3.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column3.1", type text}, {"Column3.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Column3.1"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1", "Column3.2", Splitter.SplitTextByEachDelimiter({"Closing"}, QuoteStyle.Csv, false), {"Column3.2.1", "Column3.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column3.2.1", type datetime}, {"Column3.2.2", type datetime}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Column3.2.1", "Published Date"}, {"Column3.2.2", "Close Date"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Source", each "ACT Tenders"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1","Issued by: ","",Replacer.ReplaceText,{"Column5"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Replaced Value",{{"Column5", "Agency"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Published Date", type date}, {"Close Date", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type3", "State", each "Australian Capital Territory")
in
    #"Added Custom2"

[aj1973]

Ok You need to start from scratch and before you start the Applied Steps in Power Query change this

then Apply your steps

I Published the report and the refresh worked just fine in the service with both tables

Good luck. 

You should be fine and sorry if I won't reply because it's past 9pm here.

[aj1973]

The refresh in the service worked just fine without a Gateway

[aj1973]

Hi @RoskBI 

This might be the problem

[RoskBI]

Hi Amine,

Thanks for that. I understand it's a privacy issue but unsure how I am supposed to provide privacy levels anywher else?

I have set privacy levels in Power BI Desktop and Power Bi Service through the Data Gateway. Am i missing something?

[aj1973]

You might want to look here too

[aj1973]

Hi

I am not sure whay would you need a Gateway !?

Can you check out this please

https://community.fabric.microsoft.com/t5/Service/Gateway-needed-for-web-data/m-p/723034

[RoskBI]

Hi Amine,

It's unfortunate as the websites don't have "Tables" to pull the data cleanly sorry. I need to use the create table from example tool.

As such, I need to use Web.BrowserContents which requires a gateway for refresh (Extremely frustrating haha)

[aj1973]

😀

Try Web.Contents and let me know please

[RoskBI]

@aj1973
Please see below. Unfortunately how the websites are made, the only way to grab data consistently is through the create table from example tool. 

Web.BrowserContents:

Web.Contents:

[aj1973]

Close and Apply please, then Publish