Embedded Files
Ahmed Salama | SEP, 2024
Tired of wasting time on repetitive data tasks? This blog reveals hidden power-user tricks across Excel, Power BI, Tableau, SQL, Alteryx, and SAP that most professionals never discover.
After years of using data in numerous industries, I've picked up secret Excel, Power BI, Tableau, SQL, Alteryx, and SAP tricks that most professionals are not even aware of.
These are not beginner tricks—these are life-changing shortcuts that will save you 10+ hours a week if you work with data on a daily basis.
???? Excel: Secret Power User Tips1. Flash Fill (Ctrl + E) – The Magic WandProblem: Manually splitting names, addresses, or reformatting data.Solution:
Type first occurrence of how you'd want data altered
Ctrl + E → Excel automates the pattern fill for youUse Case: Extract first names, enter dates, clean product codes in seconds.
2. Power Query Fuzzy Matching (No VLOOKUP Needed)Problem: Merging datasets that have different spelling (i.e., "Microsoft Inc" and "Microsoft Corp").Solution:
Data → Get Data → Merge Queries
Enable "Fuzzy Matching"
Specify similarity threshold (85% is optimal)Saves: Hours of manual cleanup.
3. Dynamic Array Formulas (No More Dragging Formulas!)Problem: Repeating formulas across thousands of rows.Solution:
excel=UNIQUE(FILTER(A2:B100, (B2:B100>1000))Spills results automatically
Works for SORT, FILTER, UNIQUE, SEQUENCE
???? Power BI: The Pro Moves4. DAX Measure Templates (Reuse Logic Without Rebuilding)Problem: Copying the same measures (YTD, MoM%, etc.).Solution:
Create a "Measure Template" table
Store common measures:daxSales YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])Reference them in reports
5. Bookmark Navigation (Build Interactive Dashboards Like a Pro)Problem: Static reports with no drill-through.Solution:
Create alternate views bookmarks
Add toggle buttons to switch between them
Hide/show visuals dynamically
Result: Feels like a custom app, not just a report.
6. Performance Analyzer (Find & Fix Slow Reports)Problem: Dashboards taking ages to load.Solution:
View → Performance Analyzer
Click "Start Recording"
Refresh visuals → See what's slowing you down
???? Tableau: Next-Level Speed Hacks7. Parameter Actions (Dynamic Filters Without Coding)Problem: Users want to click and filter naturally.Solution:
Create a parameter (e.g., "Top N Products")
Add a parameter action
Let users click to switch values
Example: Click a bar to show only top 5 products.
8. Set Actions (Advanced Highlighting Without LODs)Problem: Difficult "show me related data" interactions.Solution:
Create a set (e.g., "Selected Customers")
Add a set action
Let users click to dynamically update the set
Use Case: Click a region → see only customers for that region.
9. Transparent Sheets (Make Dashboards Look Pro)Problem: Fuss of ugly white backgrounds in dashboards.Solution:
Format sheet → "None" for background
Float visuals for a clean, modern look
???? SQL: Write Queries 10x Faster
10. CTEs Over Subqueries (Readable & Reusable Code)
Problem: Nested subqueries are ugly.Solution:
sqlWITH top_customers AS (SELECT * FROM customers WHERE revenue > 10000), active_orders AS (SELECT * FROM orders WHERE status = 'shipped')SELECT * FROM top_customers JOIN active_orders.
Advantages:
Easier to debug
Reusable in complex queries
11. Window Functions (Don't Use Self-Joins)Problem: Running totals or ranks are difficult to compute with ugly joins.Solution:
sqlSELECT customer_id, order_date, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_totalFROM ordersUse Cases:
Running totals
Rankings (ROW_NUMBER, RANK)
Moving averages
12. Query Optimization with EXPLAINProblem: Slow queries.Solution:
sqlEXPLAIN ANALYZE SELECT * FROM large_table WHERE.Shows execution plan
Reveals missing indexes
⚙️ Alteryx: Automate the Boring Stuff13. Batch Macro (Process 100s of Files in One Click)Problem: Cleaning the same data daily/weekly.Solution:
Build a workflow for one file
Convert to Batch Macro
Point to a folder → auto-process all files
14. Fuzzy Match Tool (Merge Dirty Data Without Exact Keys)Problem: Merging datasets with typos.Solution:
Use the Fuzzy Match tool
Set similarity threshold (e.g., 80%)
Let Alteryx fix minor mismatches
Saves: Hand VLOOKUPs for hours.
????️ SAP: Hidden Shortcuts for Power Users15. Transaction Codes (Avoid the Menu Maze)Problem: Using endless menus.Solution:
/n → Close current transaction
/o → Open new session
SE16N → Direct table access
16. Variant Saving (Reuse Reports Instantly)Problem: Re-executing the same report against varying filters.Solution:
Run a report
Save as "Variant"
Reload with one click
These are not beginner tricks—these are life-changing shortcuts that will save you 10+ hours a week if you work with data on a daily basis.
???? Excel: Secret Power User Tips1. Flash Fill (Ctrl + E) – The Magic WandProblem: Manually splitting names, addresses, or reformatting data.Solution:
Type first occurrence of how you'd want data altered
Ctrl + E → Excel automates the pattern fill for youUse Case: Extract first names, enter dates, clean product codes in seconds.
2. Power Query Fuzzy Matching (No VLOOKUP Needed)Problem: Merging datasets that have different spelling (i.e., "Microsoft Inc" and "Microsoft Corp").Solution:
Data → Get Data → Merge Queries
Enable "Fuzzy Matching"
Specify similarity threshold (85% is optimal)Saves: Hours of manual cleanup.
3. Dynamic Array Formulas (No More Dragging Formulas!)Problem: Repeating formulas across thousands of rows.Solution:
excel=UNIQUE(FILTER(A2:B100, (B2:B100>1000))Spills results automatically
Works for SORT, FILTER, UNIQUE, SEQUENCE
???? Power BI: The Pro Moves4. DAX Measure Templates (Reuse Logic Without Rebuilding)Problem: Copying the same measures (YTD, MoM%, etc.).Solution:
Create a "Measure Template" table
Store common measures:daxSales YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])Reference them in reports
5. Bookmark Navigation (Build Interactive Dashboards Like a Pro)Problem: Static reports with no drill-through.Solution:
Create alternate views bookmarks
Add toggle buttons to switch between them
Hide/show visuals dynamically
Result: Feels like a custom app, not just a report.
6. Performance Analyzer (Find & Fix Slow Reports)Problem: Dashboards taking ages to load.Solution:
View → Performance Analyzer
Click "Start Recording"
Refresh visuals → See what's slowing you down
???? Tableau: Next-Level Speed Hacks7. Parameter Actions (Dynamic Filters Without Coding)Problem: Users want to click and filter naturally.Solution:
Create a parameter (e.g., "Top N Products")
Add a parameter action
Let users click to switch values
Example: Click a bar to show only top 5 products.
8. Set Actions (Advanced Highlighting Without LODs)Problem: Difficult "show me related data" interactions.Solution:
Create a set (e.g., "Selected Customers")
Add a set action
Let users click to dynamically update the set
Use Case: Click a region → see only customers for that region.
9. Transparent Sheets (Make Dashboards Look Pro)Problem: Fuss of ugly white backgrounds in dashboards.Solution:
Format sheet → "None" for background
Float visuals for a clean, modern look
???? SQL: Write Queries 10x Faster
10. CTEs Over Subqueries (Readable & Reusable Code)
Problem: Nested subqueries are ugly.Solution:
sqlWITH top_customers AS (SELECT * FROM customers WHERE revenue > 10000), active_orders AS (SELECT * FROM orders WHERE status = 'shipped')SELECT * FROM top_customers JOIN active_orders.
Advantages:
Easier to debug
Reusable in complex queries
11. Window Functions (Don't Use Self-Joins)Problem: Running totals or ranks are difficult to compute with ugly joins.Solution:
sqlSELECT customer_id, order_date, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_totalFROM ordersUse Cases:
Running totals
Rankings (ROW_NUMBER, RANK)
Moving averages
12. Query Optimization with EXPLAINProblem: Slow queries.Solution:
sqlEXPLAIN ANALYZE SELECT * FROM large_table WHERE.Shows execution plan
Reveals missing indexes
⚙️ Alteryx: Automate the Boring Stuff13. Batch Macro (Process 100s of Files in One Click)Problem: Cleaning the same data daily/weekly.Solution:
Build a workflow for one file
Convert to Batch Macro
Point to a folder → auto-process all files
14. Fuzzy Match Tool (Merge Dirty Data Without Exact Keys)Problem: Merging datasets with typos.Solution:
Use the Fuzzy Match tool
Set similarity threshold (e.g., 80%)
Let Alteryx fix minor mismatches
Saves: Hand VLOOKUPs for hours.
????️ SAP: Hidden Shortcuts for Power Users15. Transaction Codes (Avoid the Menu Maze)Problem: Using endless menus.Solution:
/n → Close current transaction
/o → Open new session
SE16N → Direct table access
16. Variant Saving (Reuse Reports Instantly)Problem: Re-executing the same report against varying filters.Solution:
Run a report
Save as "Variant"
Reload with one click
Google Sites
Report abuse