Forum Discussion
How to create multiple tables automatically
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!
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.
4 Replies
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
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.
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.
