Multiple uses for a single file

Power Query
1

I have a general question on the Query Editor.

Can a single file be used to generate 3 different files that are needed in my PBI Report?? I have a file that contains Sales, Profit and Product Quantity Sold that are in different columns in the same Spreadsheet.

Can I reference this file 3 times to extract the Sales, Profit and Quantity Sold information into 3 files as Sales, Profit, Quantity???

Currently I’m “pulling” the original Spreadsheet apart manually and saving the results as the 3 files I need.

Any suggestions??

Guy

2

Hi @GuyJohnson. Yes … I use reference extensively, and it sounds like it would be perfect to allow you to “shape” each table accordingly.
Greg

3

@Greg

Thanx for the response.

Can you expand on “reference” a bit

Guy

4

Right-click on your imported table in PQ and select “reference”; rename the resulting table, then remove columns as desired, remove duplicates if necessary. Repeat for each of you desired “references”.
Greg

1 Like
5

@Greg

Thanx and outstanding.

Will give this a whirl. Could save me about 3 hours prep time across 8 different Reports every week.

Guy

6

Hi @GuyJohnson,

Important to note in regard to the technique mentioned by @Greg is that you have to leave your base query intact (but you can disable it’s load of course). Then right-click that base query and create a “Reference” as many times as you need ELSE use “Duplicate” instead of reference…

1 Like
7

Thanx @Melissa

8

@GuyJohnson,

Also, here’s a video on this topic that you may find helpful:

https://forum.enterprisedna.co/t/referencing-duplicating-queries/3370

  • Brian
1 Like
9

Thanx @BrianJ

10

@BrianJ @Melissa @Greg

Thanx for all the help on this problem I was having.

It took me 5 hours to rebuild my models from the single source and using reference to sub-divide.

Well worth it because my refresh time for all 7 PBI Reports is down to about 30 seconds per report.

HUGE time saver.

Guy

5 Likes