Forum Discussion

Post Patron

5 years ago

Dynamic Append Tables

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

Super User

5 years ago

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
Post Patron
5 years ago
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
  Super User
  5 years ago
  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
    Post Patron
    5 years ago
    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!