Re: Dynamics Combining of Tables

phcampbell01 · ‎10-10-2021

Hi All,

Building dataflows and want to be make the model as re-usable as possibe. I am trying to, if possible, build a merge table from a list of other tables but do dynamically.

So in the data model i have 2 tables:

- PracticeName1_Clients

- PracticeName2_Clients

The situation is that overtime we could be dealing with Practice 3, 4 and so on.

What i was thinking is that i have a table of the Practice names (PracticeName1, PracticeName2, etcc)

then using this list reference Table.Combine such that it will combine the tables based on the list.

the fixed method is Source = Table.Combine({PracticeName1_Clients, PracticeName2_CLients), want turn this into a refernce to the list but not sure if possible or the syntax.

We will have numerous tables (e.g PracticeName1_Revenue) so a lot of configuration when we do for other clients (with same data model)

Hope that make sense!

Thanks

Paul

v-jingzhang · ‎10-13-2021

Hi @phcampbell01

It seems Power Query in dataflow doesn't support keywords that well like in desktop. You could refer to this blog: Get List of Queries in Power BI - RADACAD. In comment area, Reza Rad says that "This method only works inside the Power Query Editor. the environments of #shared and #sections are not available when you load data into Power BI." Based on my test, I can combine queries well in Power BI Desktop while only get null for each table in dataflow.

Best Regards,
Community Support Team _ Jing

phcampbell01 · ‎10-13-2021

Thanks for that, thats what i had determined as well.

I looked at the article you mentioned and noted Reza says "Unfortunately, there is no way to append query names like that when you load data into Power BI unless you statically have their names somewhere."

To his last point i could have a static list of the tables ( {"ClientsTable1","ClientsTable2",ClientsTable3"). On this basis could i form a statement to combine the related tables?

Regards

CNENFRNL · ‎10-11-2021

Use Expression.Evaluate() this way,

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!