Forum Discussion

RoskBI's avatar
RoskBI
Frequent Visitor
2 years ago

Power Bi Service - Data Privacy Refresh

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...
RoskBI's avatar
RoskBI
Frequent Visitor
2 years ago

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&tenderType=&categories%5B0%5D.id=104789&categories%5B0%5D.percentage=100&issuingBusinessId=&awardedSupplier.id=&awardedSupplier.name=&openThisWeek=false&openingDateFrom=&openingDateTo=&closeThisWeek=false&closingDateFrom=&closingDateTo=&awardedThisWeek=false&awardedDateFrom=&awardedDateTo=&groupBy=NONE&page=&searchTitle= 

aj1973's avatar
aj1973
Community Champion
2 years ago

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's avatar
    RoskBI
    Frequent Visitor
    2 years ago

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

    • aj1973's avatar
      aj1973
      Community Champion
      2 years ago

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

      Give me the M Code please !

      • RoskBI's avatar
        RoskBI
        Frequent Visitor
        2 years ago

        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"