HTML Extracting data form the web

Power Query
1

I am trying to extract some data from the web, So far so good.

https://www.cwgc.org/visit-us/find-cemeteries-memorials/search-results/?Country=United+Kingdom&Lat=0&Lon=0&Locality=County+Antrim&Name=&CasualtiesRange=0&Size=1000&Page=1

However there is some data that is not in a column that I need to retrieve. From what I can see HTML.Table can only work with columns, so maybe what I want to do cannot be done.

I want to extract data-lon, data-lat and data-id

Does anyone have any suggestions please?

Thank you

<!--
```<tr data-lon="-6.21807" data-lat="54.9888" data-id="2101152" class="location-result">
                                <td>
                                    <a href="/visit-us/find-cemeteries-memorials/cemetery-details/2101152/newtowncrommelin-presbyterian-churchyard/">
                                        <div class="name">NEWTOWNCROMMELIN PRESBYTERIAN CHURCHYARD</div>
                                    </a>
                                </td>
                                <td>
                                    <a href="/visit-us/find-cemeteries-memorials/cemetery-details/2101152/newtowncrommelin-presbyterian-churchyard/">
                                        <div class="location bold">County Antrim</div>
                                        <div class="country">United Kingdom</div>
                                    </a>
                                </td>
                                <td>
                                    <a href="/visit-us/find-cemeteries-memorials/cemetery-details/2101152/newtowncrommelin-presbyterian-churchyard/">
                                        <div class="casualties">2</div>
                                    </a>
                                </td>

                                <td>
                                    <a href="/visit-us/find-cemeteries-memorials/cemetery-details/2101152/newtowncrommelin-presbyterian-churchyard/" class="button green more">More details</a>
                                        <a href="/log-in/" class="button saveToList noBg">
                                            <svg class="[ icon icon-save-listing ]">
                                                <use xlink:href="#sprite-icon-save-listing"></use>
                                            </svg>
                                            <span class="blue underline">Save to My list</span>
                                        </a>
                                </td>
                                            <svg class="[ icon icon-save-listing ]">
                                                <use xlink:href="#sprite-icon-save-listing"></use>
                                            </svg>
                                            <span class="blue underline">Save to My list</span>
                                        </a>
                                </td>
                            </tr> -->
2

Hi @kellysolutions,

Give this a go, it’s not a full solution but I’m sure you can take it from here :wink:.
Just paste this script into a new blank query

let
  Source = List.Transform(
    List.Distinct(
      List.Select(
        Text.Split(
          Web.BrowserContents(
            "https://www.cwgc.org/visit-us/find-cemeteries-memorials/search-results/?Country=United+Kingdom&Lat=0&Lon=0&Locality=County+Antrim&Name=&CasualtiesRange=0&Size=1000&Page=1"
          ), ">"
        ), each ( Text.Contains(_, "/visit-us/find-cemeteries-memorials/cemetery-details/") and not Text.Contains(_, "button green more")) or Text.Contains(_, "<tr data-lon=")
      )
    ), each Text.Trim(Text.Clean(_))
  ), 
  ToTable = Table.FromColumns(
    { List.Alternate(Source, 1, 1), List.Alternate(Source, 1, 1, 1) }, 
    {"Site", "GPS Coordinates"}
  )
in
  ToTable

.
Result

image
image1298Ă—282 36.1 KB

I hope this is helpful

3 Likes
3

Hi @kellysolutions, we’ve noticed that no response has been received from you since October 30, 2021. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Hi @kellysolutions, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.

5

Hi Melissa,

Thank you very much for your help.

I finally got a chance to work with it yesterday.

Terrific as always.

Can I ask you just one other question please? When you have to encapsulate a special character such as ", is there a particular way to do this?

e.g. They looked out to sea and one them said, “what a beautiful sunset”. They all agreed.

If I want to find “what a beautiful sunset”, would I use ““what a beautiful sunset””. I can’t quite get it to work

Thank you again,

Paul