Forum Discussion
Iterate Web.Contents
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!
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
4 Replies
v-yulgu-msftMicrosoft Employee
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
Cool works great, how do i restrict "
AddMonth = Table.AddColumn(Years, "Month", each {1..12})," from 1 to current month
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
