Dynamic Append Tables

[ngct1112]

Hi, I append 5 tables from excel in my query to get the consolidated table.

However, users would like to see the result from different combinations from those 5 tables.

May I know is there a way the users can dynamically choose which tables to append in my queries?

Great thanks!

[amitchandak]

@ngct1112 , You can create common dimensions like date and join it with all file table and then you can create measures in each table and user can add as per need .

or

Add table name in power query as a new column; before you append the tables. And provide that as one of the filter /slicer

[ngct1112]

@amitchandak Sorry for my misleading. In this case, I am not putting the Data in PowerBI but excel only.
Is it possible the users can choose which tables to be appended by doing filter in the excel sheet? Thanks.

[amitchandak]

@ngct1112 , Assuming you are importing 5 excel. And they're power bi table now.

You can merge in power query as data prep, of the union in Dax. But that all is data prep.

This means If I want to choose two tables from the slicer and add/union. You will not be able to create a table dynamically and create a new table.

Measure can only use slicer value

so we can define a dynamic union table DAX measure and use that

or we can add table name as one of the columns in the source and use that slicer to give the feel that user is able to select the table

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[ngct1112]

@amitchandak I have attached the simpified Excel file. Sample Excel File 

What I am trying to do is a dynamic way for the user to select which tables to be included(appended) in the Result tab.

And NO powerbi will be involved. 

Great thanks!