locked
How to Process multiple URL with single Query. RRS feed

  • General discussion

  • Hello,

    Not sure if this has to go in Excel Forum or in Power BI, As this is with regards to Power Query, I am putting this question in Power Query.

    I have to extract data for about 50 categories from one url (URLs Stored in Excel) with different parameter for the same Query. At this time I have created different queries as connection for each parameter and has combined in one table which is working fine at this time, but I feel this is inefficient to have 50 connections and a new category is in place we have to ensure to create a new connection. Is there any way to iterate through the URL List and generate the table with one Query without having many connections.

    URL in Excel sheet are as below where only one parameter MF is different

    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=39&frmdt=01-Jan-2018&todt=23-Nov-2018
    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=3&frmdt=01-Jan-2018&todt=23-Nov-2018
    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=50&frmdt=01-Jan-2018&todt=23-Nov-2018
    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=1&frmdt=01-Jan-2018&todt=23-Nov-2018
    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=53&frmdt=01-Jan-2018&todt=23-Nov-2018
    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=4&frmdt=01-Jan-2018&todt=23-Nov-2018
    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=36&frmdt=01-Jan-2018&todt=23-Nov-2018
    http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=59&frmdt=01-Jan-2018&todt=23-Nov-2018

    Thanks in advance.

    Raji.

    Saturday, November 24, 2018 2:56 AM

All replies

  • Raji

    One way with List.Transform. With your URLs in Table1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"myURLs", type text}}),
        listOfTables = List.Transform(#"Changed Type"[myURLs], each
            Web.Page(Web.Contents(_)){0}[Data]),
        listAsTable = Table.FromList(listOfTables, Splitter.SplitByNothing(),
            {"tablesToCombine"}, null, ExtraValues.Error),
        // Get column names from 1st table to combine
        columnNames = Table.ColumnNames(listAsTable[tablesToCombine]{0}),
        tablesToCombineExpanded = Table.ExpandTableColumn(listAsTable, "tablesToCombine",
            columnNames, columnNames)
    in
        tablesToCombineExpanded

    Notes:

    • You might need to adapt the end (bolded) of: Web.Page(Web.Contents(_)){0}[Data]
    • I assumed all URL tables have the same number of columns & names


    • Edited by Lz._ Sunday, November 25, 2018 2:38 PM Clarification
    Sunday, November 25, 2018 9:22 AM
  • Thanks for the Tip, I have used your Tip List.Transform and completed the task.
    Monday, November 26, 2018 7:37 AM
  • Thanks for the Tip, I have used your Tip List.Transform and completed the task.

    You're welcome. As you seem to be new here: when a (or more) suggestion(s) helped/solved your problem marking it/them as such can help others with same/similar problems. Thanks in advance
    Monday, November 26, 2018 7:42 AM
  • Ya, I do not see the link or button to say it is a solution and hence could not mark it as solved.
    Monday, November 26, 2018 8:35 AM
  • At the bottom of each reply there are several hyperlinks (Reply | Quote | …). One of them should be something like Mark as answer
    Monday, November 26, 2018 8:43 AM