cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors