# 🌈 xl-pq-handler
> 🧩 A Pythonic Power Query (.pq) File Manager for Excel & Power BI Automation
[](https://pypi.org/project/xl-pq-handler/)
[](https://pypi.org/project/xl-pq-handler/)
[](LICENSE)
---
### 🧠 What is `xl-pq-handler`?
`xl-pq-handler` is a **Python UI App + library** built for developers, data analysts, and automation engineers who work with **Power Query (.pq)** files in Excel or Power BI.
It lets you:
- 🔍 Parse, search, and index `.pq` scripts
- 📋 Copy Power Query code to clipboard
- 🪄 Insert queries directly into Excel workbooks
- 🧾 Maintain YAML-based metadata (name, category, tags, description, version)
- 🔁 Export, validate, and refresh PQ indexes
- ⚡ Batch-insert queries for rapid Excel automation
All from Python. No manual clicks. No clutter. 🚀
---
> Stop the cap. Managing Power Query `.pq` files is low-key a nightmare.
>
> **This tool is the ultimate glow-up for your M-code.** 💅
>
> It's not just a library; it's your new **Power Query IDE**.
---
## 💅 The Vibe Check: Before vs. After
_(The PQ IDE You Didn't Know You Needed ✨)_
| **Before xl-pq-handler 🫠** | **After xl-pq-handler 😎** |
| :-------------------------------------------- | :------------------------------------------------------ |
| Endless copy-pasting M-code | One-click insert into **any** open Excel workbook |
| Forgetting `fn_Helper_v3` needs `fn_Util_v1` | **Dependency graph** shows you the whole family tree 🌳 |
| decentralized file organization | Auto-organized folders based on `category` |
| Editing metadata = Manual YAML torture | Right-click -\> **Edit Metadata** -\> Save -\> Done ✅ |
| "Which file uses that API?" -\> 🤷♂️ | **Data Sources tab** spills the tea ☕ |
| Blindly extracting queries from huge files | **Preview** _before_ you extract, with syntax colors\! |
| Updating one function in 5 workbooks manually | Edit once -\> Refresh UI -\> Insert where needed |
This is that **main character energy** for your data workflow.
---
## ✨ Features That Absolutely Slap
This ain't your grandpa's script library. We got a whole ecosystem:
### 🖥️ **The UI App (Your New Dashboard)**
- Launch a **dedicated desktop app** straight from your terminal. No more sad script outputs.
- Visually browse, search, and filter your _entire_ `.pq` library like a pro.
- It's got that dark mode aesthetic. You know the vibes. ✨
### 📥 **Smart Extract ("Yoink\! Button")**
- **From File:** Point it at _any_ `.xlsx` / `.xlsm` / `.xlsb` and instantly rip out all the Power Queries.
- **From Open Workbook:** Got 5 Excels open? No stress. A **dropdown lists all open workbooks**. Pick one, hit extract. Easy.
- **Preview Before Saving:** See the code (with syntax highlighting\!), parameters, and data sources _before_ you commit to saving the `.pq` file. No more blind extraction\!
### 🪄 **Dependency-Aware Insert ("Yeet Button")**
- Select a query (e.g., `FinalReport`). The app automatically knows it needs `GetSalesData` and `fn_FormatDate`.
- It yeets **all required queries** into Excel _in the correct order_. 🤯
- **Target Practice:** Don't just spray into the active workbook. Use the **dropdown to select _exactly_ which open workbook** gets the queries. Precision\!
### ✏️ **Edit Metadata + Auto-Sync ("The Organizer")**
- Right-click a query -\> "Edit Metadata."
- Change the `name`, `tags`, `dependencies`, `description`, `version`.
- **The Magic ✨:** Change the `category` from `Staging` to `Production`? The app **automatically moves the `.pq` file** to the `Production/` folder. Chef's kiss\! 🤌
### 💅 **Syntax Highlighting ("Make it Pretty")**
- See your M-code in the **Preview tabs** (Library, Edit, Extract) with **VS Code-style syntax highlighting**. Keywords, functions, strings, comments – all colored up. ✨
### 🧐 **Code Intelligence ("The Brain")**
- **Parameter Peek:** Select a function query, and the **"Parameters" tab** shows its inputs, types (`any`, `text`, etc.), and if they're `optional`.
- **Data Source Detective:** The **"Data Sources" tab** scans the code and lists out _all_ the external connections (`Sql.Database`, `Web.Contents`, `File.Contents`, etc.) and whether the source is a literal string or an input parameter. Big for security audits\! 🕵️♀️
- **Dependency Deets:**
- **Auto-Detect:** Click the button in the Edit dialog to automatically scan the code and suggest the `dependencies`. Saves _so_ much typing.
- **Visual Graph:** The **"Graph" tab** shows a slick tree view of a query's entire dependency chain. No more surprises. 🌳
### 💻 **External Editor Escape Hatch ("Send It")**
- Need to tweak the _actual_ M-code logic?
- Right-click -\> "Open in Editor."
- Instantly opens the `.pq` file in **VS Code** (if it's in your PATH) or falls back to Notepad. Edit, save, hit refresh in the UI. Seamless.
### 🤖 **Python Backend (`PQManager`)**
- All the power, none of the clicks. Import `PQManager` into your own Python automation scripts.
- Headless extraction, insertion, index building – you name it. Perfect for CI/CD or scheduled tasks.
---
## 📦 Get it Already (Installation)
```bash
pip install xl-pq-handler
```
_(This single command grabs everything you need: `customtkinter`, `xlwings`, `pydantic`, `pyyaml`, `pandas`, `filelock` – the whole squad.)_
---
## 🚀 How to Vibe
### 1\. The Main Way (The UI) 💅
This is the main event. Open your terminal:
```bash
# Better launch - point it at your actual PQ repo folder
python -m xl_pq_handler "D:\Path\To\Your\PowerQuery_Repo"
# Or even better way
pqmagic "D:\Path\To\Your\PowerQuery_Repo"
```
Now just... use the app. Click around. It's built different. 😎
Then just... click buttons. It's that easy.
### 2\. 🤓 Script Kiddie Corner (Python Usage)
For your `main.py` automation scripts, use the `PQManager`.
```python
from xl_pq_handler import PQManager
# Point manager at your repo
manager = PQManager(r"D:\Path\To\Your\PowerQuery_Repo")
# Rebuild index (always a good move)
manager.build_index()
# ---- EXAMPLE: INSERT INTO SPECIFIC WORKBOOK ----
target_workbook = "Monthly_Report_WIP.xlsx" # Must be open!
queries_needed = ["Calculate_KPIs", "Generate_Summary"]
try:
manager.insert_into_excel(
names=queries_needed,
workbook_name=target_workbook # <-- Target acquired 🎯
)
print(f"🚀 Sent queries to {target_workbook}. Mission accomplished.")
except Exception as e:
print(f"😭 Insert failed: {e}")
# ---- EXAMPLE: EXTRACT FROM FILE ----
source_file = r"C:\Downloads\NewDataSource.xlsx"
try:
manager.extract_from_excel(category="Downloaded", file_path=source_file)
print(f"✅ Successfully yoinked queries from {source_file}!")
except Exception as e:
print(f"💀 Extraction failed: {e}")
```
---
## 📁 The Drip (File Structure)
This is how you organize your repo. The app does the rest.
```
My-Power-Query-Repo/
│
├── index.json <-- The app makes this. Don't touch.
│
├── API/ <-- "API" Category
│ ├── Get_API_Data.pq
│ └── fn_Get_Credentials.pq
│
├── Helpers/ <-- "Helpers" Category
│ ├── fn_Format_Date.pq
│ └── fn_Safe_Divide.pq
│
└── Reports/ <-- "Reports" Category
└── Final_Sales_Report.pq
```
Each `.pq` file is just M-code with a **YAML "frontmatter"** block at the top. This is the metadata.
```yaml
---
name: Clean_RawSales # The query's name in Excel/PBI
category: Staging # Matches the folder name (keep it sync'd!)
tags: [cleaning, sales, raw] # Searchable tags
dependencies: # List other queries *this one* calls
- fn_FormatDate
description: Cleans and transforms the raw monthly sales data dump. # What it does
version: 2.1 # Your version number
---
let # Start of your actual M-code
Source = Csv.Document(File.Contents("path/to/raw.csv"), ...),
#"Formatted Date" = fn_FormatDate(Source, "OrderDate")
in
#"Formatted Date"
```
---
## 📜 License
This project is licensed under the **GNU-GPL 3.0 License**. Go wild.
---
## 💚 Credits
Made by **Sudharshan TK** (tks18)
If this tool just saved your workflow, give it a ⭐ **Star on [GitHub](https://github.com/tks18/xl-pq-handler)\!**
---
> ⚡ _“Automate the boring Power Query stuff — one `.pq` at a time.”_
---
Raw data
{
"_id": null,
"home_page": null,
"name": "xl-pq-handler",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.13",
"maintainer_email": null,
"keywords": "Power Query, Excel, Power BI, Automation, Analytics, Audit",
"author": "tks18",
"author_email": "tks18 <tksudharshan@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/1b/92/51fefca9024f135d70dfb4eae7ad33fb950d882c6c360b07f5f060d16c43/xl_pq_handler-2.4.1.tar.gz",
"platform": null,
"description": "# \ud83c\udf08 xl-pq-handler\n\n> \ud83e\udde9 A Pythonic Power Query (.pq) File Manager for Excel & Power BI Automation\n\n[](https://pypi.org/project/xl-pq-handler/)\n[](https://pypi.org/project/xl-pq-handler/)\n[](LICENSE)\n\n---\n\n### \ud83e\udde0 What is `xl-pq-handler`?\n\n`xl-pq-handler` is a **Python UI App + library** built for developers, data analysts, and automation engineers who work with **Power Query (.pq)** files in Excel or Power BI.\n\nIt lets you:\n\n- \ud83d\udd0d Parse, search, and index `.pq` scripts\n- \ud83d\udccb Copy Power Query code to clipboard\n- \ud83e\ude84 Insert queries directly into Excel workbooks\n- \ud83e\uddfe Maintain YAML-based metadata (name, category, tags, description, version)\n- \ud83d\udd01 Export, validate, and refresh PQ indexes\n- \u26a1 Batch-insert queries for rapid Excel automation\n\nAll from Python. No manual clicks. No clutter. \ud83d\ude80\n\n---\n\n> Stop the cap. Managing Power Query `.pq` files is low-key a nightmare.\n>\n> **This tool is the ultimate glow-up for your M-code.** \ud83d\udc85\n>\n> It's not just a library; it's your new **Power Query IDE**.\n\n---\n\n## \ud83d\udc85 The Vibe Check: Before vs. After\n\n_(The PQ IDE You Didn't Know You Needed \u2728)_\n\n| **Before xl-pq-handler \ud83e\udee0** | **After xl-pq-handler \ud83d\ude0e** |\n| :-------------------------------------------- | :------------------------------------------------------ |\n| Endless copy-pasting M-code | One-click insert into **any** open Excel workbook |\n| Forgetting `fn_Helper_v3` needs `fn_Util_v1` | **Dependency graph** shows you the whole family tree \ud83c\udf33 |\n| decentralized file organization | Auto-organized folders based on `category` |\n| Editing metadata = Manual YAML torture | Right-click -\\> **Edit Metadata** -\\> Save -\\> Done \u2705 |\n| \"Which file uses that API?\" -\\> \ud83e\udd37\u200d\u2642\ufe0f | **Data Sources tab** spills the tea \u2615 |\n| Blindly extracting queries from huge files | **Preview** _before_ you extract, with syntax colors\\! |\n| Updating one function in 5 workbooks manually | Edit once -\\> Refresh UI -\\> Insert where needed |\n\nThis is that **main character energy** for your data workflow.\n\n---\n\n## \u2728 Features That Absolutely Slap\n\nThis ain't your grandpa's script library. We got a whole ecosystem:\n\n### \ud83d\udda5\ufe0f **The UI App (Your New Dashboard)**\n\n- Launch a **dedicated desktop app** straight from your terminal. No more sad script outputs.\n- Visually browse, search, and filter your _entire_ `.pq` library like a pro.\n- It's got that dark mode aesthetic. You know the vibes. \u2728\n\n### \ud83d\udce5 **Smart Extract (\"Yoink\\! Button\")**\n\n- **From File:** Point it at _any_ `.xlsx` / `.xlsm` / `.xlsb` and instantly rip out all the Power Queries.\n- **From Open Workbook:** Got 5 Excels open? No stress. A **dropdown lists all open workbooks**. Pick one, hit extract. Easy.\n- **Preview Before Saving:** See the code (with syntax highlighting\\!), parameters, and data sources _before_ you commit to saving the `.pq` file. No more blind extraction\\!\n\n### \ud83e\ude84 **Dependency-Aware Insert (\"Yeet Button\")**\n\n- Select a query (e.g., `FinalReport`). The app automatically knows it needs `GetSalesData` and `fn_FormatDate`.\n- It yeets **all required queries** into Excel _in the correct order_. \ud83e\udd2f\n- **Target Practice:** Don't just spray into the active workbook. Use the **dropdown to select _exactly_ which open workbook** gets the queries. Precision\\!\n\n### \u270f\ufe0f **Edit Metadata + Auto-Sync (\"The Organizer\")**\n\n- Right-click a query -\\> \"Edit Metadata.\"\n- Change the `name`, `tags`, `dependencies`, `description`, `version`.\n- **The Magic \u2728:** Change the `category` from `Staging` to `Production`? The app **automatically moves the `.pq` file** to the `Production/` folder. Chef's kiss\\! \ud83e\udd0c\n\n### \ud83d\udc85 **Syntax Highlighting (\"Make it Pretty\")**\n\n- See your M-code in the **Preview tabs** (Library, Edit, Extract) with **VS Code-style syntax highlighting**. Keywords, functions, strings, comments \u2013 all colored up. \u2728\n\n### \ud83e\uddd0 **Code Intelligence (\"The Brain\")**\n\n- **Parameter Peek:** Select a function query, and the **\"Parameters\" tab** shows its inputs, types (`any`, `text`, etc.), and if they're `optional`.\n- **Data Source Detective:** The **\"Data Sources\" tab** scans the code and lists out _all_ the external connections (`Sql.Database`, `Web.Contents`, `File.Contents`, etc.) and whether the source is a literal string or an input parameter. Big for security audits\\! \ud83d\udd75\ufe0f\u200d\u2640\ufe0f\n- **Dependency Deets:**\n - **Auto-Detect:** Click the button in the Edit dialog to automatically scan the code and suggest the `dependencies`. Saves _so_ much typing.\n - **Visual Graph:** The **\"Graph\" tab** shows a slick tree view of a query's entire dependency chain. No more surprises. \ud83c\udf33\n\n### \ud83d\udcbb **External Editor Escape Hatch (\"Send It\")**\n\n- Need to tweak the _actual_ M-code logic?\n- Right-click -\\> \"Open in Editor.\"\n- Instantly opens the `.pq` file in **VS Code** (if it's in your PATH) or falls back to Notepad. Edit, save, hit refresh in the UI. Seamless.\n\n### \ud83e\udd16 **Python Backend (`PQManager`)**\n\n- All the power, none of the clicks. Import `PQManager` into your own Python automation scripts.\n- Headless extraction, insertion, index building \u2013 you name it. Perfect for CI/CD or scheduled tasks.\n\n---\n\n## \ud83d\udce6 Get it Already (Installation)\n\n```bash\npip install xl-pq-handler\n```\n\n_(This single command grabs everything you need: `customtkinter`, `xlwings`, `pydantic`, `pyyaml`, `pandas`, `filelock` \u2013 the whole squad.)_\n\n---\n\n## \ud83d\ude80 How to Vibe\n\n### 1\\. The Main Way (The UI) \ud83d\udc85\n\nThis is the main event. Open your terminal:\n\n```bash\n\n# Better launch - point it at your actual PQ repo folder\npython -m xl_pq_handler \"D:\\Path\\To\\Your\\PowerQuery_Repo\"\n\n# Or even better way\npqmagic \"D:\\Path\\To\\Your\\PowerQuery_Repo\"\n```\n\nNow just... use the app. Click around. It's built different. \ud83d\ude0e\n\nThen just... click buttons. It's that easy.\n\n### 2\\. \ud83e\udd13 Script Kiddie Corner (Python Usage)\n\nFor your `main.py` automation scripts, use the `PQManager`.\n\n```python\nfrom xl_pq_handler import PQManager\n\n# Point manager at your repo\nmanager = PQManager(r\"D:\\Path\\To\\Your\\PowerQuery_Repo\")\n\n# Rebuild index (always a good move)\nmanager.build_index()\n\n# ---- EXAMPLE: INSERT INTO SPECIFIC WORKBOOK ----\ntarget_workbook = \"Monthly_Report_WIP.xlsx\" # Must be open!\nqueries_needed = [\"Calculate_KPIs\", \"Generate_Summary\"]\n\ntry:\n manager.insert_into_excel(\n names=queries_needed,\n workbook_name=target_workbook # <-- Target acquired \ud83c\udfaf\n )\n print(f\"\ud83d\ude80 Sent queries to {target_workbook}. Mission accomplished.\")\nexcept Exception as e:\n print(f\"\ud83d\ude2d Insert failed: {e}\")\n\n# ---- EXAMPLE: EXTRACT FROM FILE ----\nsource_file = r\"C:\\Downloads\\NewDataSource.xlsx\"\ntry:\n manager.extract_from_excel(category=\"Downloaded\", file_path=source_file)\n print(f\"\u2705 Successfully yoinked queries from {source_file}!\")\nexcept Exception as e:\n print(f\"\ud83d\udc80 Extraction failed: {e}\")\n```\n\n---\n\n## \ud83d\udcc1 The Drip (File Structure)\n\nThis is how you organize your repo. The app does the rest.\n\n```\nMy-Power-Query-Repo/\n\u2502\n\u251c\u2500\u2500 index.json <-- The app makes this. Don't touch.\n\u2502\n\u251c\u2500\u2500 API/ <-- \"API\" Category\n\u2502 \u251c\u2500\u2500 Get_API_Data.pq\n\u2502 \u2514\u2500\u2500 fn_Get_Credentials.pq\n\u2502\n\u251c\u2500\u2500 Helpers/ <-- \"Helpers\" Category\n\u2502 \u251c\u2500\u2500 fn_Format_Date.pq\n\u2502 \u2514\u2500\u2500 fn_Safe_Divide.pq\n\u2502\n\u2514\u2500\u2500 Reports/ <-- \"Reports\" Category\n \u2514\u2500\u2500 Final_Sales_Report.pq\n```\n\nEach `.pq` file is just M-code with a **YAML \"frontmatter\"** block at the top. This is the metadata.\n\n```yaml\n---\nname: Clean_RawSales # The query's name in Excel/PBI\ncategory: Staging # Matches the folder name (keep it sync'd!)\ntags: [cleaning, sales, raw] # Searchable tags\ndependencies: # List other queries *this one* calls\n - fn_FormatDate\ndescription: Cleans and transforms the raw monthly sales data dump. # What it does\nversion: 2.1 # Your version number\n---\n\nlet # Start of your actual M-code\n Source = Csv.Document(File.Contents(\"path/to/raw.csv\"), ...),\n #\"Formatted Date\" = fn_FormatDate(Source, \"OrderDate\")\nin\n #\"Formatted Date\"\n```\n\n---\n\n## \ud83d\udcdc License\n\nThis project is licensed under the **GNU-GPL 3.0 License**. Go wild.\n\n---\n\n## \ud83d\udc9a Credits\n\nMade by **Sudharshan TK** (tks18)\n\nIf this tool just saved your workflow, give it a \u2b50 **Star on [GitHub](https://github.com/tks18/xl-pq-handler)\\!**\n\n---\n\n> \u26a1 _\u201cAutomate the boring Power Query stuff \u2014 one `.pq` at a time.\u201d_\n\n---\n",
"bugtrack_url": null,
"license": null,
"summary": "A Pythonic Power Query (.pq) File Manager for Excel & Power BI Automation",
"version": "2.4.1",
"project_urls": {
"Changelog": "https://github.com/tks18/xl-pq-handler/releases",
"Documentation": "https://github.com/tks18/xl-pq-handler#readme",
"Homepage": "https://github.com/tks18/xl-pq-handler",
"Issues": "https://github.com/tks18/xl-pq-handler/issues",
"Repository": "https://github.com/tks18/xl-pq-handler"
},
"split_keywords": [
"power query",
" excel",
" power bi",
" automation",
" analytics",
" audit"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "2bd2a562a8b1dfee07da8ffeb410b36b89b2471db7ac32a0c6da2fe224efb916",
"md5": "0c6460c6ea4f8c2a5307c212e66d4945",
"sha256": "92159d0cc32e1f37a964c3e9762e40999c3c84f4657f209870aba00fe6f6e224"
},
"downloads": -1,
"filename": "xl_pq_handler-2.4.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "0c6460c6ea4f8c2a5307c212e66d4945",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.13",
"size": 54550,
"upload_time": "2025-10-29T07:29:11",
"upload_time_iso_8601": "2025-10-29T07:29:11.018237Z",
"url": "https://files.pythonhosted.org/packages/2b/d2/a562a8b1dfee07da8ffeb410b36b89b2471db7ac32a0c6da2fe224efb916/xl_pq_handler-2.4.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "1b9251fefca9024f135d70dfb4eae7ad33fb950d882c6c360b07f5f060d16c43",
"md5": "abd99fb604f87a61648bb42c0aae6ea1",
"sha256": "a3c25ad07ad99acec9dccaaadb00d3d4cc1acbbb2717868472bdfec677bfe45c"
},
"downloads": -1,
"filename": "xl_pq_handler-2.4.1.tar.gz",
"has_sig": false,
"md5_digest": "abd99fb604f87a61648bb42c0aae6ea1",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.13",
"size": 37496,
"upload_time": "2025-10-29T07:29:12",
"upload_time_iso_8601": "2025-10-29T07:29:12.428878Z",
"url": "https://files.pythonhosted.org/packages/1b/92/51fefca9024f135d70dfb4eae7ad33fb950d882c6c360b07f5f060d16c43/xl_pq_handler-2.4.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-29 07:29:12",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "tks18",
"github_project": "xl-pq-handler",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "xl-pq-handler"
}
JSON
