Forum Discussion
Dynamically generate query tables
I'm importing data from an Azure Blob, and then generating some queries to share on the online portal. One of my intermediate queries generates a table of [name][table] like:
Query: ALL TABLES name | table ------------------- name1 | Table1 name2 | Table2 name3 | Table3
The names are unique (grouped by name in previous query step), and the table links all point to a table I want to use in a new query. Currently I'm having to manually generate new queries from each Table, and then apply a custom query function on the query source to get the desired final query to export, eg.:
Query: NAME1
let
Source = #"ALL TABLES",
#"Name1" = DoSomething(Source, 0)
in
#"Name1"
Query: NAME2
let
Source = #"ALL TABLES",
#"Name2" = DoSomething(Source, 1)
in
#"Name2"
etc.
This is ok for now as we're setting up, we don't have that many entries. However as soon as we start putting real data into the Azure Blob the number of rows and queries we need to generate will start growing, and doing it by hand is un-manageable.
Ideally I'd be able to add a last step to the ALL TABLES query, that goes through all the rows and generates new queries from the tables in them. This list will also potentially change with each update, so ideally this should automatically generate the new queries when the data is reloaded form the Azure Blob.
Is there a way to do this from a Power BI query? Alternatively, does anyone know where I might look on how to make an external script generate this on a power BI project (say, an automated script run daily from our build server)?
14 Replies
If I may paraphrase to see if I understand what you are trying to achieve (more or less). You have a bunch of tables in and new tables will be added periodically and you want a way to grabs all of the data in those tables and automagically recognize when new tables are added and pull that into the data model during refresh?
Is that correct?
This is a good explanation of how to do it: http://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/
Just replace the reference to the converted list of 1-7 by your query "All Tables". You will actually reate a new column in which the "DoSometing" will be executed for every row of your "All Tables".
ImkeF - That seems to effectively be a way to grab data from many sources and merging it all into a single table where the first column defines a 'page', and you could filter by page to look at each of the individual 'tables'. Did I get that right? It's quite different from my problem unfortunately.
Greg_Deckler - Not sure if I fully understood your paraphrase, but the use of 'automagically' leads me to think you got what I meant, and this isn't really feasible :)
To re-iterate (and add a bit of background):
We are storing custom user triggered event data in an Azure Blob - each event type will have different parameters for us to measure.
I have a table with 2 columns, [name] holds the unique event names, and [name table] is a table column whose rows point to event data for a particular event type. All tables have different data in them, eg.:All Tables:
Event Name | Event Data
-------------------------
Add Geometry | Table
File Saved | Table
Registration | TableAdd Geometry-Table: id | Outline | Filled ---------------------------- 0 | Dashed | False 2 | Solid | True File Saved-Table: id | Date | Format | Size ------------------------------------- 3 | 01/01 | txt | 15 15 | 02/05 | ppt | 100 Registration-Table: id | Source --------------- 10 | website 45 | e-mail
(note- the immediate tables linked are actually different, that's why I run a query function on them to get the final form, but let's ignore that step).
ImkeF - as you can possibly see, having all this data merged into a single table would make it extremely sparse and filled with nulls
What I'd want to do, is from this 'All Tables' table, generate separate new tables in my model. Right now I need to manually create a new query every time, and set the correct entry in 'All tables' as the source (or actually right click on the table link and 'Add as new query'). Ideally I would be able to have those query tables generated automatically, rather than manually, so that if we add new events we don't have to go through and create new tables every time.
I believe you can modify the model by refreshing the data in the desktop app- eg. if a table has columns generated by pivoting another column, depending on what data comes in on that column will define which new columns are created. Whether we can generate new tables in the model in a similar automatic fashion is what I'm trying to figure out.Sorry 1: Yes - completely misread your request.
Sorry 2: No idea how to tackle this
:-(
Would table.split work?
Never mind. I have a similar dilemma and realized that this isn't solvable with just Power Query. Looking around the we, the answers seems to be to generate dynamic queries based on a list of values using VBA and pass the full set of m code into each one in the vba code..... though unless there is a specific reason for storing the queries in PQ, it might be easier to vba the entire solution.
Hi jPinhao,
Now, so many years further, have you ever found a way of doing it in PQ?
