SharePoint can be either your best “single source of truth”… or the reason your refreshes fail on Monday morning. This guide shows how to structure SharePoint properly (sites, libraries, lists), set permissions that scale, and connect everything cleanly to Excel  and Power BI —with practical “do this / don’t do that” patterns. PRACTICE MATERIAL BELOW! Check out some of our FREE templates and materials. 1) SharePoint basics that matter for BI people Sites, Libraries, Lists (t

If you’ve ever wanted cleaner dashboards, faster “at-a-glance” reading, and less “wall of numbers” fatigue—Unicode symbols + hex colors are a surprisingly powerful combo. In this post you’ll learn practical, copy-paste-ready tricks for Excel , Power BI (DAX + conditional formatting) , and Power Query . PRACTICE MATERIAL BELOW! Check out some of our FREE templates and materials. Why this matters (real-life use cases) Status columns that actually read well : ✅ OK, ⚠ Warning, ❌

Overview When you connect Power Query / Power BI to a SharePoint site (or document library), there are (at least) two common M-functions you may see or use: SharePoint.Files(siteUrl as text, optional options as record) as table SharePoint.Contents(siteUrl as text, optional options as record) as table Although they sound similar, their behavior, performance, metadata returned, and navigation experience differ. Understanding their differences helps in building more robust and p

Hook:  If your model “mysteriously” changes numbers, breaks relationships, or sorts months alphabetically—blame data types. What we’ll cover:  where to define types (and why), the safest order of operations across the ETL chain, Power Query vs. Model vs. Table View, special cases (DirectQuery, currency, duration), and concrete, copy-paste examples. 1) Why data types matter (and where they live) Power Query (PQ):  Data shaping stage. Types here affect folding, transformations

Hook:  Hard-coded paths are silent project killers. Move a file, share a workbook, or publish a PBIX—and boom: refresh fails. Goal:  In ~10 minutes you’ll set up robust, parameter-driven paths for Excel (Power Query) and Power BI (Desktop & Service), plus a tiny, reusable M pattern you can paste into any project. 1) Why hard-coded paths fail When you embed "C:\Users\Alice\Project\data.csv" in a query, anyone whose files live elsewhere (e.g., D:\Work\Data\) will get errors. I

Goal:  a fast, self-updating Power BI report that tracks earthquakes in the European–Mediterranean region (with a global table for comparison), highlights activity hotspots, and surfaces clean KPIs for decision-making. Scope: Earthquakes in the European-Mediterranean region with magnitude 4.5 or greater within the last 720 days or the last 2 years. Source: ETH Zürich - Swiss Seismological Service (HTML daily data): http://www.seismo.ethz.ch/en/earthquakes/europe/last90daysMa

Power Query is often seen as a tool for just loading and cleaning data — but it offers far more than the basics. Most users rely on a...

Power BI’s Power Query (M language) allows creating custom functions and reusable query steps to simplify common tasks. Below are five...

Want faster refreshes and snappier previews? Here’s a practical, no-nonsense guide to making Power Query load much  quicker using only...

Have you ever received an email with a link to download a CSV or Excel file, and wished it could just go straight into your Excel sheet...

When you're transforming data in Power BI or Excel using Power Query, you're bound to run into errors —mismatched data types, division by text, nulls, you name it. But instead of manually inspecting row by row, why not automate error detection with a custom Power Query function ? In this blog post, I’ll walk you through a clean method to catch and analyze all row-level errors  using Power Query. This approach works great when importing or transforming large datasets. We’ll co

Power Query  is Microsoft’s powerhouse tool for data transformation and automation, built into Excel and Power BI. If you’ve ever spent...

Function:  Would you like to clean multiple columns in a single operation? This custom step allows you to clean as many columns as...

Function:  Would you like to rename multiple columns in a single operation? This custom step allows you to rename as many columns as...

Remove Empty Rows and Columns from tables by using Power Query custom functions. No need to load empty columns anymore.

Function:  Converts different date formats into YYYY-MM-DD or any other format that you define. You define it in code, it executes! Date column with mixed date formats Usage: = ConvertToISODate("03/13/2025") → Returns: "2025-03-13" Power Query code to use and reuse: let ConvertToISODate = (inputDate as any) as nullable text => let ConvertedDate = try Date.ToText(Date.From(inputDate), "yyyy-MM-dd") otherwise null in ConvertedDate Perfectly cleaned date

Function:  Extracts only the numeric values from a given text. Simply add custom function in Power Query and define your column. Add a new Query from Sources Usage: = ExtractNumbers("Order ID: 123ABC456") → Returns: "123456" Power Query code for use and reuse: let ExtractNumbers = (inputText as text) as text => let NumbersOnly = Text.Select(inputText, {"0".."9"}) in NumbersOnly in ExtractNumbers Choose your column with a text/number to clean (

Function:  This function, RemoveSpecialChars, cleans a text string by removing special characters, retaining only letters and numbers. Add fx to the blank query (Advanced Editor) Usage Example:   Using = RemoveSpecialChars("H3ll0! W@rld#") will return "H3ll0 Wrld". The function operates by removing specified special characters from the input text, ensuring a clean output. Power Query code for use and reuse: RemoveSpecialChars = (inputText as text) as text => let C

This Power Query function  dynamically generates a calendar table  based on a given start date, end date, and fiscal year start month . It is designed to support time-based analysis  in Power BI by including key date attributes, fiscal calculations, and various helper columns. Power Query Calendar Function diagram illustrating parameter inputs for start date, end date, and fiscal start month, with outputs like date, fiscal year, ISO week, and day classifications such as weeke