How to create multiple tables automatically

[Erikestrela]

Hi Experts,

I need to create more than 700 queries each one with its respective hyperlink, specific querie name and same script for all.

This is what I have done:

1 - In Power Query,  new source from web and add the hyperlink

 2 - Click on advanced Editor and paste the script like:

   let
   Source = Web.BrowserContents("https:/xxxxx"),
   #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
   #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column1"}),
   #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
   #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Date", type datetime}, {"Open", type 
   in
   #"Filled Down"

3 - Renamed the Queries with text copied by the first row of first column.

And repeated this process for many times. 

Is there any way to automate this via script, R, python or any another way?

I have the name of the queries, the scrip for each one but not sure of how to create this type of macro in power bi.

Thanks in advance! 

[amitchandak]

@Erikestrela , see if these can help

https://community.powerbi.com/t5/Desktop/Loop-Through-Several-Values-Change-API-Call-URL-and-Insert-...

https://community.powerbi.com/t5/Power-Query/Looping-API-Requests-references-other-queries-or-steps-...

[lbendlin]

Are you expecting to be able to cram all these queries into the same .pbix file? Or will you create one file per query?

Here's what comes to mind:

- template apps

- REST API

- VBA to autogenerate the Power Query code

- modify the .pbix file directly via Powershell

[Erikestrela]

Hi @lbendlin 

I am expecting cram all these queries into same .pbix file. They are google sheets that are updated daily. After importing I intent to use UNION dax . I estimate having a .pbix with about 70mb after connecting all.

Not sure if Power Bi will support that. 

[lbendlin · Accepted solution]

Probably not.  But it's worth trying.

What I would do is have a reference table with the list of Google Sheets URLs, and then create a custom Power Query function that pulls the data for each Google sheet from the list. Then simply combine the resulting binaries which gives you the UNION ALL for free. Don't even think of trying to do a UNION.