Forum Discussion
Gurpreet Singh
Nov 28, 2017Copper Contributor
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...
JLUK8741
Jan 30, 2019Copper Contributor
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.
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
Jan 30, 2019Diamond Contributor
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.
- JLUK8741Jan 31, 2019Copper Contributor
Hi,
That would be my thoughts. Thank you so much for your support. Hope I would get it learnt.
- SergeiBaklanJan 31, 2019Diamond Contributor
Hi,
When make transpose within the function, please see attached
- JLUK8741Jan 31, 2019Copper Contributor
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.