Passing source folder path as parameter to query code in Power Query

Photo by Zoshua Colah on Unsplash

Step 1. Prepare standard query table

Step 2. Create table in working Excel file

  • Index
  • Source — my custom name of the folder
  • Path — folder, where files are stored
  • Tab_name — name of the tab, which contains necessary data

Step 3. Create custom function to load folder path from config table

let Parameter=(TableName as text,RowNumber as number) =>let     Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],     value = Source{RowNumber-1}[Path]in     valuein Parameter
  • TableName —name of the config table that contains folder path (this table was created in previous step),
  • RowNumber — number of the row, which contains specific folder path.

Step 4. Create intermediate query table to load folder content

let
Source = Folder.Files(fParam("tableConfig", 1))
in
Source

Step 5. Replace static data source path with paths from dynamic table

Source = Folder.Files("D:\New folder (2)"),
Source = OrderList,

--

--

--

Java Software Developer, DevOps newbie, constant learner, podcast enthusiast.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Google Sign In Authentication in Flutter Web

Machine Learning API with Django

Announcing DFinance Testnet and Airdrop Round I

IS YOUR SUPPLY CHAIN PREPARED FOR DISRUPTION? — #1 Cloud Inventory® Software as a Service

Custom loss function with Tensorflow.

Mixed Content Warnings in Google Chrome

Arduino RC Arm Vehicle

The Infiniverse Land System

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Wojciech Krzywiec

Wojciech Krzywiec

Java Software Developer, DevOps newbie, constant learner, podcast enthusiast.

More from Medium

How To Ingest Data from Excel (.xlsx) to Azure Server Database

Fixing Query Folding with SQL Server in Power BI for Incremental Refresh

What is Power BI: Architecture, Components, and Features Explained

Create a Custom Connector for Consumption Logic Apps using the Azure Portal