Iterate Web.Contents

[Anonymous]

I have a table which has column URL which contains API endpoint URL with different parameters (start and end dates). If I try to use it directly with Web.Contents it gives error:

Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=Table
Type=Type

Than I access only URL column as list with [URL] and I receive this error:

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=List
Type=Type

If I select record from table [URL]{9}, for example, than I can get data. I understand what is happening but I cannot figure out how to iterate this process so each record from column URL is passed to Web.Contents. Could You please point me in right directions cause I am extremely new to power query M.
Thanks in advance!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please try to modify M code as below:

let
    StartYear = 2018,
    EndYear = 2019,
    Years = Table.FromList({StartYear..EndYear}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
    AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
    ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
    CreateStartDate = Table.AddColumn(ShowMonths, "StartDate", each #date([Column1],[Month],1), type date),
    CreateEndDate = Table.AddColumn(CreateStartDate, "EndDate", each Date.EndOfMonth([StartDate]), type date),
    RequestHeaders = [Accept="application/json" , Authorization="Token xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"],
    OptionsRecord = [Headers=RequestHeaders],
    #"Changed Type" = Table.TransformColumnTypes(CreateEndDate,{{"StartDate", type text}, {"EndDate", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "StartDate2", each Text.Combine({Text.From([Column1], "lv-LV"), "-", Date.ToText(Date.From([StartDate]), "MM"), "-", Text.Middle(Text.From([Column1], "lv-LV"), 1, 2)}), type text),
    #"Added Custom Column1" = Table.AddColumn(#"Added Custom Column", "EndDate2", each Text.Combine({Text.From([Column1], "lv-LV"), "-", Date.ToText(Date.From([EndDate]), "MM"), "-", Date.ToText(Date.From([EndDate]), "%d")}), type text),
    #"URL" =  Table.AddColumn(#"Added Custom Column1", "URL", each "API ENDPOINT URL?start_date="&[StartDate2]&"&end_date="&[EndDate2]&"", type text),
    #"Response" = Table.AddColumn(#"URL", "Custom2", each Web.Contents([URL]))
in
   #"Response"

Best regards,

Yuliana Gu

[gingercat123]

Cool works great, how do i restrict "

AddMonth = Table.AddColumn(Years, "Month", each {1..12}),

" from 1 to current month

[Anonymous]

Yes, this works as I expected. Thank You for Your help!

[Anonymous]

There is a code I am working with, played around with List.Transform closer but still cannot get rid of errors.

let
    StartYear = 2018,
    EndYear = 2019,
    Years = Table.FromList({StartYear..EndYear}, Splitter.SplitByNothing(), null,null, ExtraValues.Error),
    AddMonth = Table.AddColumn(Years, "Month", each {1..12}),
    ShowMonths = Table.ExpandListColumn(AddMonth, "Month"),
    CreateStartDate = Table.AddColumn(ShowMonths, "StartDate", each #date([Column1],[Month],1), type date),
    CreateEndDate = Table.AddColumn(CreateStartDate, "EndDate", each Date.EndOfMonth([StartDate]), type date),
    RequestHeaders = [Accept="application/json" , Authorization="Token xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"],
    OptionsRecord = [Headers=RequestHeaders],
    #"Changed Type" = Table.TransformColumnTypes(CreateEndDate,{{"StartDate", type text}, {"EndDate", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "StartDate2", each Text.Combine({Text.From([Column1], "lv-LV"), "-", Date.ToText(Date.From([StartDate]), "MM"), "-", Text.Middle(Text.From([Column1], "lv-LV"), 1, 2)}), type text),
    #"Added Custom Column1" = Table.AddColumn(#"Added Custom Column", "EndDate2", each Text.Combine({Text.From([Column1], "lv-LV"), "-", Date.ToText(Date.From([EndDate]), "MM"), "-", Date.ToText(Date.From([EndDate]), "%d")}), type text),
    URL =  Table.AddColumn(#"Added Custom Column1", "URL", each "API ENDPOINT URL?start_date="&[StartDate2]&"&end_date="&[EndDate2]&"", type text)[URL],
    Response = List.Transform(URL, each Web.Contents(URL, OptionsRecord)),
in
    Response