Business Professionals
Excel Brushup | Power BI | DAX | Copilot | Claude AI
VB Programming | Report Automation |
MS-Office Automation
60+ Formulas | Dynamic Dashboards | MIS Reports | Excel Models
Adv. Excel | MIS Reports | Power Query
Techno-Business Professionals
Power BI | Adv. DAX | MIS Reporting | SQL | VBA |
Web Scrapping | API Integration | Python | Copilot | Claude AI
Power BI | Power Apps | Power Automate |
Power Pages | Dataverse | Copilot | Claude AI
Power BI | MIS Reporting | SQL | VBA | Web Scrapping | API Integration | Python | Power Apps | Power Automate | Power Pages | Dataverse |
Copilot | Claude AI
Business Professionals
Excel Brushup | Power BI | DAX | Copilot | Claude AI
VB Programming | Report Automation |
MS-Office Automation
60+ Formulas | Dynamic Dashboards | MIS Reports | Excel Models
Adv. Excel | MIS Reports | Power Query
Techno-Business Professionals
Power BI | Adv. DAX | MIS Reporting | SQL | VBA |
Web Scrapping | API Integration | Python | Copilot | Claude AI
Power BI | Power Apps | Power Automate |
Power Pages | Dataverse | Copilot | Claude AI
Power BI | MIS Reporting | SQL | VBA | Web Scrapping | API Integration | Python | Power Apps | Power Automate | Power Pages | Dataverse | Copilot | Claude AI
LEARN THIS HANDS ON
Azure Data Engineering with Power BI
Most Power BI teams hit the same wall: reports are slow, data refreshes break after every schema change, and nobody is sure which version of a file is the “real” one. You’ve probably got an Azure Data Lake already, but without a clear medallion architecture, it behaves more like a dumping ground than a platform. This guide walks through a concrete way to design a robust medallion architecture on Azure Data Lake, optimised for Power BI, and shows how to move from brittle file-based reporting to a stable, governed setup.
Imagine a retail analytics team with:
raw-data in Azure Data Lake Storage Gen2.Pain points:
We’ll redesign this into a medallion architecture (Bronze/Silver/Gold) on Azure Data Lake, with Power BI consuming only curated layers.
Before touching folders and pipelines, align on a few principles:
Separation of concerns by layer
Storage abstraction
Power BI-centric decisions
Governance by design
For our retail team, we’ll use a single ADLS Gen2 account adl-retail-analytics with hierarchical namespaces enabled.
At the top level:
bronze/silver/gold/Inside each layer, organise by domain and then by dataset:
bronze/
sales/
pos/
year=2026/month=10/day=09/pos_20261009_regionA.csv
product/
master/
snapshot_date=2026-10-09/product_master_20261009.json
silver/
sales/
pos/
year=2026/month=10/pos.parquet
product/
master/
snapshot_date=2026-10-09/product_master.parquet
gold/
sales/
pos_daily/
year=2026/month=10/pos_daily.parquet
inventory/
inventory_daily/
year=2026/month=10/inventory_daily.parquet
Key decisions:
year/month/day for transactional data).pos.parquet, product_master.parquet) while partitions change beneath them.This structure gives the team a predictable path for each dataset and a clear mental model: Bronze is ingestion, Silver is clean tables, Gold is report-ready fact/summary tables.
Bronze should:
Assume the team uses Azure Data Factory (ADF) or Synapse pipelines to land data.
For each source (POS, product master, inventory):
bronze/<domain>/<dataset>/<partition>.No schema enforcement at Bronze beyond what the copy activity needs to write the file. If the POS vendor adds a column, Bronze still ingests it.
This stabilises ingestion so Power BI never depends directly on source quirks.
Silver is where most of the data engineering work happens:
You can implement Silver using:
The choice depends on your platform. For ADLS + Power BI teams without Fabric, Synapse or Databricks is common.
Below is a simplified PySpark job that reads Bronze POS CSV files, enforces schema, and writes partitioned Parquet to Silver:
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date, lit, current_date
from pyspark.sql.types import StructType, StructField, StringType, IntegerType, DecimalType, DateType
spark = SparkSession.builder.getOrCreate()
bronze_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/bronze/sales/pos/"
silver_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/silver/sales/pos/"
# Define enforced schema for POS
pos_schema = StructType([
StructField("transaction_id", StringType(), False),
StructField("store_id", StringType(), False),
StructField("product_id", StringType(), False),
StructField("quantity", IntegerType(), True),
StructField("gross_amount", DecimalType(18, 2), True),
StructField("transaction_date", StringType(), False), # will be converted
StructField("region", StringType(), True)
])
# Read all Bronze POS files
bronze_df = spark.read \
.option("header", "true") \
.schema(pos_schema) \
.csv(bronze_path)
# Basic cleaning and type conversions
silver_df = bronze_df \
.withColumn("transaction_date", to_date(col("transaction_date"), "yyyy-MM-dd")) \
.withColumn("load_date", current_date())
# Write to Silver as partitioned Parquet
silver_df.write \
.mode("overwrite") \
.partitionBy("transaction_date") \
.parquet(silver_path)
What this does for the team:
silver/sales/pos having consistent types.You’d repeat this pattern for product master and inventory, including joins to create conformed dimensions (e.g., ensuring product IDs align across datasets).
Gold is where you design fact and dimension tables that match how Power BI models are built:
The goal is to minimise complex joins and heavy calculations inside Power BI.
Assume we want a daily sales fact table summarised by store, product, and date.
Using PySpark again:
from pyspark.sql.functions import sum as _sum
silver_pos_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/silver/sales/pos/"
gold_pos_daily_path = "abfss://data@adl-retail-analytics.dfs.core.windows.net/gold/sales/pos_daily/"
silver_pos_df = spark.read.parquet(silver_pos_path)
pos_daily_df = silver_pos_df.groupBy(
"transaction_date",
"store_id",
"product_id",
"region"
).agg(
_sum("quantity").alias("total_quantity"),
_sum("gross_amount").alias("total_gross_amount")
)
pos_daily_df.write \
.mode("overwrite") \
.partitionBy("transaction_date") \
.parquet(gold_pos_daily_path)
Gold tables like pos_daily are what Power BI datasets will consume. They are:
Once Bronze/Silver/Gold are in place, you need to connect Power BI in a way that respects the architecture.
Power Query (Import mode) directly to ADLS
Synapse serverless SQL over ADLS
This example uses the Azure Data Lake Storage Gen2 connector in Power BI Desktop.
let
Source = AzureDataLakeStorage.DataLake("https://adl-retail-analytics.dfs.core.windows.net"),
GoldSales = Source{[Name="data"]}[Content],
PosDailyFolder = GoldSales{[FolderPath="/gold/sales/pos_daily/"]}[Content],
PosDailyTable = Parquet.Files(PosDailyFolder),
ExpandedData = PosDailyTable[Data],
Result = Table.TransformColumnTypes(ExpandedData,{
{"transaction_date", type date},
{"store_id", type text},
{"product_id", type text},
{"region", type text},
{"total_quantity", Int64.Type},
{"total_gross_amount", type number}
})
in
Result
What matters here:
For larger setups, you might prefer Synapse serverless with external tables; Power BI then connects via SQL, which is often more familiar to data teams and helps centralise access control.
Medallion architecture only helps if access is controlled consistently.
Bronze: Restricted to data engineering and platform teams.
Silver: Accessible to data engineers and BI developers.
Gold: Accessible to broader analytics teams and Power BI service principals.
This prevents analysts from accidentally pointing reports at Bronze and bypassing the curated layers.
Once the architecture is live, the retail team’s biggest ongoing challenge is schema change and data volume growth.
Bronze absorbs changes: new columns appear in raw files without breaking ingestion.
Silver jobs should:
Gold jobs depend on Silver and should be updated only after Silver is stable.
For Power BI Import mode consuming Gold:
Having Gold partitioned by date makes it easier to design incremental refresh policies that align with the underlying data layout.
Before:
raw-data.After:
If your Power BI reports still point at raw CSVs in Azure Data Lake, pick one critical dataset and move it into a Bronze–Silver–Gold pattern first; once you see the stability and refresh improvements from that single change, you’ll have a concrete blueprint to extend the medallion architecture across the rest of your lake.
This article reflects a shift from ad-hoc file-based reporting to a structured medallion architecture on Azure Data Lake, particularly relevant for Power BI-centric teams that need predictable refresh behaviour and governed data layers.
Professionals who want to apply these patterns to their own data can explore Excelgoodies' Data Engineering & BI Azure (On Cloud) programme - taught live by instructors, with certification awarded once a real project is running at work.
Insights compiled through ongoing industry research and discussions within the Excelgoodies Analytics Community.
Data Engineering
New
Next Batches Now Live
Power BI
SQL
Power Apps
Power Automate
Microsoft Fabrics
Azure Data Engineering