Forum Discussion
Dynamic url connection
I second what MarkLaf has said: To help you, we need to see the exact details for your function/query.
I have tried the function with a parameter but I still get the same error.
This is my data funete, it contains all the urls in JSON:
= Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vSwdO2Z5bz5axku2XRhWtLHhkVwTC2zNCcelMFxO_d5iXFNQeiKzNsjwSq6DOSLSf4xgqbekRw9Rvyu/pub?output=xlsx"), null, true)
And this is my invoked custom function with one url :
= () => let
Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vSwdO2Z5bz5axku2XRhWtLHhkVwTC2zNCcelMFxO_d5iXFNQeiKzNsjwSq6DOSLSf4xgqbekRw9Rvyu/pub?output=xlsx"), null, true),
#"Hoja 1_Sheet" = Source{[Item="Hoja 1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Hoja 1_Sheet",{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"links", type text}})
in
#"Changed Type1"
This is the content desired of each url:
I believe ImkeF was talking about using a function on each url, assuming there were multiple get & transform steps you needed to do on each. The function you've set up is simply accessing the Google sheet and promoting headers - it's not doing anything with a url.
Since all the urls are pointing to a json doc, I don't think you need to try anything that fancy. I think the following should work for you:
let Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vSwdO2Z5bz5axku2XRhWtLHhkVwTC2zNCcelMFxO_d5iXFNQeiKzNsjwSq6DOSLSf4xgqbekRw9Rvyu/pub?output=xlsx"), null, true), #"Hoja 1_Sheet" = Source{[Item="Hoja 1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"Hoja 1_Sheet", [PromoteAllScalars=true]), GetJson = Table.AddColumn(#"Promoted Headers", "data", each Json.Document(Web.Contents([links]))), ExpandJson = Table.ExpandRecordColumn(GetJson, "data", {"id", "url", "user_id", "value", "item_id", "item_type", "created_at", "updated_at", "Column9"}) in ExpandJsonNote that this is the aproximate equivalent of using the UI to:
- Load in the workbook and select Hoja 1_Sheet
- "Use First Row as Headers" button to promote headers
- Add a custom column (in Add Column ribbon) with "data" as the name and "Json.Document(Web.Contents([links]))" as the custom formula
- Expand custom column and extract all attributes
Does that help?
