Forum Discussion
Dynamically generate query tables
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
:-(
Facing a similar issue. Did you find any solution to this?
Hi Roshna. Unfortunately no, I didn't find a direct solution to this. As far as I know this isn't doable within Power BI, only if you actually have an external DB where you store your data, and then serve Power BI the different tables (and even then I'm guessing you'd still need to make a new query table to hold data from the different tables).
We did however solve this in a different way, which might be helpful to you depending on how similar your issue is to ours:
By pivoting / unpivoting the property columns and their values we've created a table Dim_EventProperties which has 3 columns Property ID, Property, Value - mapping each property to all it's possible values:
| Property ID | Property | Value | | 0 | A | x | | 1 | A | y | | 2 | B | z | | 3 | C | xyz |
We then have a bridge table between the events fact table, and the attached properties, eg. :
(Fact_UserAction) | ID | Action Name | Time | | 0 | ActionA | 05.08 | | 1 | ActionB | 05.08 | | 2 | ActionA | 04.08 | (Bridge_ActionToProperties) | Action ID | Property ID | | 0 | 0 | | 0 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 2 |
In this way we can still map actions to many different properties, and we can still slice by different property names or values. It does make it slightly awkward to do so as it's not as easy to report on (but can be achieved if you set up you reports in a certain way). It also prevents you from looking at particular property uses depending on values of other properties (unless you start defining multiple similar tables, as if they were multiple dimensions).
TL : DR - No solution to the original issues, found a non-ideal work-around
