Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jPinhao
Helper II
Helper II

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)?

13 REPLIES 13
ape-n
New Member

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. 

ImkeF
Super User
Super User

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".

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 - 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 | Table

 

 Add 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.

Facing a similar issue. Did you find any solution to this?

@Roshna we now found a solution for this, however it only works at the data loading stage.

 

Using the R connector, you can read your data source and generate data frames dynamically in R, which are then loaded into Power BI. If you want more info on how we did this let me know.

@jPinhao I realize this is an old thread, but would appreciate any insight into how one can achieve this via R. Shoot my way if you can! Thanks greatly

Hi, I'm also interested in how you dynamically created data frames in R and uploaded to Power BI.  Could you post or forward?  Thanks!

 

-Chuck

I'm also interested in dynamically creating query tables using R data frames.  Can you send along the solution?  Thanks!

Hi JPinhao,

 

I would be interested in how you solved this issue using R, could you provide me with the details please?

 

Thanks

 

 

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

Sorry 1: Yes - completely misread your request.

Sorry 2: No idea how to tackle this

😞

 

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

Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.