Load same data for multiple tables

[Anonymous]

I am looking to see if there is an easier way to do this. I am loading a folder of Excel files but I am doing it 3 different times because I need to filter out different information to retain the information that I am looking for that specific table. Is there a way to load the information once and create the 3 tables on queries built after that data is loaded so it doesn't need to load all the excel files 3 times?

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Anonymous]

My original question was not resolved.

What I am really looking for is to load a file lets call 'Data' and it be the data source for 3 different queries without having to load the same data 3 times and use Query Editor to give me 3 similar but different data sets (This comes into play when you load a bunch of files but you only want to retain certain pieces of information from each file based on partitioning the data with the files). I typically build the data loading queries for users but they are not skilled Power BI users so I try to keep the data tables simplified so they are familiar with what they are looking at to allow them to build their reports as needed. 

Best example I can give load folder called 'Data' (~30 files). 

Table 1: Filter data to leave only Week 1 data for all files

Table 2: Filter data to leave only Week 2 data for all files

Table 3: Filter data to leave only Week 3 data for all files

**I know this can be accomplished without creating three tables but it is easier for the analyst to understand this way rather than seeing all of the data meshed together and having to use filters when building Reports. Just seeing if this is possible.

[MisterFry]

I encountered a similar issue/requirement. 

What you need to do is load the data from the file into a source table, and then in DAX (not in power query) define calculated tables that satisfy your requirement: 

https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables

In this way, the source table is loaded once, and then copied into calculated tables without re-executing the query to pull the data. 

[MisterFry]

I encountered a similar issue/requirement. 

What you need to do is load the data from the file into a source table, and then in DAX (not in power query) define calculated tables that satisfy your requirement: 

https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables

In this way, the source table is loaded once, and then copied into calculated tables without re-executing the query to pull the data. 

[v-danhe-msft]

Hi @Anonymous,

Based on my research, you could ues the "Combine files" function in Power BI.

Reference:https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries

Regards,

Daniel He

 

[indhu]

Hi @Anonymous,

Once the table is loaded in power BI you can use duplicate option instead of loading it thrice. If you had already tried this and want something else maybe you can have a general table and set filters on a visual level/ page level. 

[Anonymous]

indhu,

The only duplicate option I see is the option to duplicate the query but it will still be loading it thrice. Is there an option to just duplicate the data and not the query?

[indhu]

@Anonymous Please check this Copying a data table. If this is what you meant for. 

Regards,

Indhu