Excel linking to query in excel!

[Babycakes_00]

Hi all

I have a query in an excel doc named "Document 1.xlsx"

This refreshes daily.

This is too slow to load into my sheet on sharepoint so I am referencing it by ='[Document 1.xlsx]Sheet 1'!$B2 in my "Document 2.xlsx"

However as soon as data is added in Document 1, Column B (e.g. B3)

It doesn't refresh in my new document "Document 2.xlsx", I have to refresh the column by re-entering ='[Document 1.xlsx]Sheet 1'!$B2 down the column.

There is no lookup. as this is the first field the rest of my lookup is based on.

Is there a way to load this information without loading the query direcly?

Thanks heaps

[v-veshwara-msft]

Hi @Babycakes_00 ,
We haven't heard from you since last response and just wanted to check whether the solutions provided have worked for you. If yes, please accept as solution to help others benefit. If not, please reach out anytime, we're happy to help!
Thank you.

[v-veshwara-msft · Accepted solution]

Hi @Babycakes_00 ,
Thanks for posting in Microsoft Fabric Community.
To refresh data automatically you can use Power Automate.
Steps:
1. Create a Power Automate Flow:
Create a new flow:
    Choose Automated Cloud Flow.
    Use the trigger "When a file is created or modified in a folder" (SharePoint connector).

2. Configure the trigger
Set the Site Address:
Select or manually enter the URL of the SharePoint site where the  folder for Document 1 is located.
          Set the Folder:
          Choose the folder containing Document 1.

3. Get File Content From Document 1

Add the Get File Content action (SharePoint connector).
Configure the action:
         Site Address: Same as the trigger.
         File Identifier: Shared documents>Document 1

4. Update Document 2

1. Add the Update action (SharePoint connector).

2. Configure the action:

   Site Address: Select the SharePoint site where Document 2 is stored.
   Folder Path: Choose the folder containing Document 2.
   File Name: Document 2
   File Content: Use the dynamic content from the Get File Content action.
   
   
   
   

5. Test the flow
          Update Document 1 
          Confirm the flow runs automatically in Power Automate.
          The changes appear in Document 2 in SharePoint.

Hope this helps.

If this post helps , please accept as solution to help others find easily and a Kudos would be greatly appreciated.

Best Regards,
Vinay.

[Omid_Motamedise]

This is because of your steps in Power Query, when you load data from other excel file, after selecting the sheets, you willl select the collumn names should be expanded, as the newly added column is not part of sellected column name list, it will not loaded, 

[Babycakes_00]

That's confused me.

Document 1 is the query and all data is refreshing  periodically. Column b is refreshing in the document .

the document 2 is where I am I am trying to retrieve from document 1

if that's what you mean this will continue to happen. How can I get the data out and pulled into my new document?

a power automate or other? 

[Omid_Motamedise]

Oh sorry, it seems I miss understand your initial question.

[Babycakes_00]

Thank you is there a way I can make this work.