Getting Data from Multiple Web Pages -- Replicate changes made using Query Editor

I am getting data from Multiple Web pages into multiple excel worksheets. Each Web page has the same format, and the same changes need to be made to each worksheet using the Query Editor before I loa...

BI & Data Analytics

Need Help

JLUK8741

Copper Contributor

Jan 30, 2019

Hi Sergei,

thank you for your reply but i don't have much knowledge on this part.

this is the page i would extract. http://www.cic.hk/common/srs/subcontractorDetail.aspx?id=R000001

i hope to capture content from id=R000001 to R0014500. Would you mind to show me the steps setting up?

thank you.

SergeiBaklan

Diamond Contributor

Jan 30, 2019

The function itself is simple, it just extract information for given ID

(pID as text) =>
let
    Source = Web.Page(Web.Contents(pURL,
        [Query = [id=pID]])),
    Data0 = Source{0}[Data]
in
    Data0

Question is what to do with that. As an example in the query I generated list of ID:s from 1 to 100, picked-up related info with function and expanded everything in one table

let
    Numbers = {1..100},  // ID:s to receive
    RegNumbers = List.Transform(Numbers, each "R" & Text.Start("000000",6-Text.Length(Text.From(_))) & Text.From(_)),
    ListToTable = Table.FromList(RegNumbers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenameToId = Table.RenameColumns(ListToTable,{{"Column1", "ID"}}),
    AddRegInfoTables = Table.AddColumn(RenameToId, "Custom", each fnRegistrationParticulars([ID])),
    ExtractRegNumbers = Table.AddColumn(AddRegInfoTables, "Custom.1", each List.First([Custom][Column2])),
    FilterEmpty = Table.SelectRows(ExtractRegNumbers, each ([Custom.1] <> "")),
    ExpandInfo = Table.ExpandTableColumn(FilterEmpty, "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    KeepInfo = Table.SelectColumns(ExpandInfo,{"Column1", "Column2"})
in
    KeepInfo

Even with hundred ID:s it takes time - each individual request takes few seconds. Other option could be request them one by one if only few are required.

Please check attached.

JLUK8741
Copper Contributor
Jan 31, 2019
Hi,

That would be my thoughts. Thank you so much for your support. Hope I would get it learnt.
SergeiBaklan
Diamond Contributor
Jan 31, 2019
Hi,

When make transpose within the function, please see attached
JLUK8741
Copper Contributor
Jan 31, 2019
Dear Sergei,

Much pleasure for your kind help. May I have help if how to load the format like attached?

- each record (id) start in new row

Thank you.

Forum Discussion

Getting Data from Multiple Web Pages -- Replicate changes made using Query Editor

Resources