Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jracer007
Helper IV
Helper IV

Dynamic url connection

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?

Opera Instantánea_2018-01-31_152020_millicom-my.sharepoint.com.png

10 REPLIES 10
Greg_Deckler
Super User
Super User

@ImkeFand @MarcelBeug will be your best bets for this.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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: 

power bi_diego5.png

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:

  1. Load in the workbook and select Hoja 1_Sheet
  2. "Use First Row as Headers" button to promote headers
  3. Add a custom column (in Add Column ribbon) with "data" as the name and "Json.Document(Web.Contents([links]))" as the custom formula
  4. Expand custom column and extract all attributes

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.