Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I have a list of more than 200 url to which I must connect to obtain data. Is there any way to do this quickly without having to connect again and again?
@ImkeFand @MarcelBeug will be your best bets for this.
Add a new column to your table where your call the URL for each row.
Or turn all of your operations per URL into a function and apply it like described in here: https://www.youtube.com/watch?v=RXm1MoxD1UA&t=94s
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeF Expression.Error: Evaluation resulted in a stack overflow and cannot continue.
I second what @MarkLaf has said: To help you, we need to see the exact details for your function/query.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 ExpandJson
Note that this is the aproximate equivalent of using the UI to:
Does that help?
his house of 200 url's
Hi!!
@MarkLaf Formula.Firewall: Query 'Hoja 1' (step 'ExpandJson') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
What custom function are you trying to use? Did you confirm it works with just one instance before applying to all? Does the custom column step time out even with something simple like =Json.Document(Web.Contents([url])) ?
How can I create the function properly?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |