Appending Tables in Direct Query Mode

[Splush]

Hello there,

So I know there is no way on appending tables in Power Query right now but there has to be some workaround at least for this specific case here. So Im working with messages that are passed along a hierachical Identifier (like CEO001DEP002CLUSTER005). Each and every message has one of those identifiers and an option field about how it is supposed to be shared (like "non-disclosing", "pass one level up", "pass one level down" and "pass on to all levels down").

Since all of these sharing options need to be filtered in a different way, I have created one table for each option. But all of these options have to go back in to the same table so the user can see all messages that are relevant. Since now I have tried it with anti-Joins, appending all tables together and having every sharing option as a seperate field in one table (but this will duplicate all other messages as soon as I extract the tables). All former lead to the query switching to import mode.

Is there any option on how to work around this? See the table diagram attached when this explanation was somehow stupid 😄

Thank you very much!

[Splush · Accepted solution]

Okay, I finally got it to work. Its not completely live though and requires a filter to be changed or something in order to refresh:

So I kept all these tables and added a column with a table Identifier (A, B, C, ...) on the other hand I created a new Dataverse table with just these Identifiers and loaded it into my query. Now I just had to do a left-join and had all my tables loaded. Using a conditional column I was able to combine the resulting columns from the different tables into one for each type.

So now PowerBi at least shows this table as being live and the information is refreshing on a change of filter or refresh of the dashboard - it should be good for my usecase though

[v-yanjiang-msft]

Hi @Splush ,

Sorry I'm not very clear about the logic of the above tables. How the Emitter value came from in the final table.

Best Regards,
Community Support Team _ kalyj

[Splush]

Hey @v-yanjiang-msft ,

thank you so much for your response!

What I forgot to mention is that I use a list of all available identifiers as base for my tables. So I join all messages that this Identifier should have access to by the different sharing options. When I join the messages they contain their Identifier which I just rename as "Emitter"