Excel Power Query_Automatically load a table from another excel file using its file path

[ccs2869]

Hi 

I need some help with power query.

I created a power query that automatically import several files as it from the file paths in the cells.

Most of them work fine.

Except below one, the source file is a table. so in the Applied Steps, I have to manually click on "Table" to expand it.

So what I want to do is when I plug in the file path to the cell(TestPlan), I want the power query automatically load everything on the source file into a Test Plan tab. 

however, here is the error message 

Here is my code, Please let me know how I am supposed to adjust this to make it work. Thank you for any help!!

let
FilePath = Excel.CurrentWorkbook(){[Name="TestPlan"]}[Content]{0}[Column1],
RuleName = Excel.CurrentWorkbook(){[Name="RuleName"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(FilePath), null, true),
TestPlan = Source{[Item="RuleName",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(TestPlan,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"TEST PLAN: TEXT", type text}, {"EXPECTED RESULT", type text}, {"ACTUAL RESULT", type text}, {"PASS OR FAIL", type text}})
in
#"Changed Type2"

[p45cal · Accepted solution]

Separately from my other reply, I note you have the likes of

FilePath = Excel.CurrentWorkbook(){[Name="TestPlan"]}[Content]{0}[Column1]

which looks like you're getting the first value in the first column of a 'table' which is a (single celled?) named range in Excel?

If so, and if this might ever be used in an international setting, an error might be thrown because not all languages use Column1 as the first column name (in France for example, it's Colonne1), so you might consider using

FilePath = Table.FirstValue(Excel.CurrentWorkbook(){[Name="TestPlan"]}[Content])

which doesn't use any column name. You could probably do the same with "RuleName".

[p45cal]

You have the line:

TestPlan = Source{[Item="RuleName",Kind="Sheet"]}[Data],

You have a step RuleName not used anywhere else in the query.

Could it be that the double quotes need to be removed leaving:

TestPlan = Source{[Item=RuleName,Kind="Sheet"]}[Data],

Just a guess, I've not done more than glance at the code.

[V-yubandi-msft]

Hi @ccs2869 ,

Thanks for reaching out to the Microsoft Fabric Community Forum.

We understand your problem, and there is an easier way to automatically load a table from another Excel file using its file path in Excel Power Query. Follow these simple steps using SharePoint and Power BI Desktop.

follow below mentioned steps

• Navigate to your Excel file in SharePoint. Copy the file path.
• Open Power BI Desktop.
• Go to the “Get Data” option.
• Select “Web” as the data source
• Paste the copied Excel file path.

•   

  
  • Click OK.
  • Choose the “Organizational Account” option.
  • Sign in with your credentials.
  • Click “Connect.”
  • Complete the authentication process.

The Navigator page will open.

• If you need to transform the data, click “Transform Data” to open the Power Query Editor.
• If no changes are needed, load the data as it is, and it will be redirected to Power BI Desktop.

Use the data in Power BI Desktop to create visuals based on your data.

If you need data transformations, you can go power query editor.

After creating your visual report, publish it in Power BI.

• Set up a schedule to refresh your data in Power BI according to changes in your Excel sheet.
• Your data will refresh according to the schedule, ensuring that any changes in your Excel data are reflected in your visualizations.

• Every second you can refresh your data using schedule refreshers.by your requirements.
• If u change any changes in your xl data visualization also will change accordingly.

By following these steps, you can simplify the process and ensure your data is always up-to-date automatically load a table from another Excel file using its file path.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

[PwerQueryKees]

Can you explain a bit more on what you are trying to do?

You mention most work fine, except one. Are the files in defferent formats?

You say "when I plug in the file path to the cell(TestPlan)", what do you mean? The a cell does not have a file path. So what file path are you referring to?
I am guessing here:

• You have an excell sheet with a table
• The table has a column with the file path of each file you want to process
• One of the files has a different format

What is not clear:

• Are you processing all files at once?
• What do you want to do with the results?
• Are all files in the same folder? Or in all different folders?

Etc...

[ccs2869]

there are several source files - 2 .rul, 2 txt, 1 docx,  1 xls or 1 xlsx that need to be loaded onto a template

5 power queries to automatically load these files based on a cell where I manually put in a folder path. The folder contains above source files/

However, the xls/xlsx file doesn't work. I can manually load this through "Get Data" ... but I want it to load/update automatically when I put in the folder path in the cell.

1) copy folder path to the cell (FolderPath) on template

2) the template will generate 5 file paths in below cells for those source files. (One of those I call that "TestPlan")

3) Refresh Power Query on Template to update imported source files based on the file paths generated in Step 2.

4 of them work fine, except TestPlan.

I have several other tabs that generate analysis using those imported source files. 

[PwerQueryKees]

Can you show the reuilt of the Source step? And a screenshot of the Excel where the file names are?

Two things to check for:

• Are there no secret spaces? It often happens that an extra space before or after a value is overlooked and causes mismatches when compared to a value without the spaces.
• Power query is case sensitive, also when comparing values in a query. So even the filename has to match case with the value you put in your cells.

[Omid_Motamedise]

Can you sahre the table in the result of Source step?

[Omid_Motamedise]

I means just cliick on the table and see itss data below