Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Yes, this works as I expected. Thank You for Your help!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |