Data Governance, Security, Ethics, and Compliance

Introduction

Business intelligence is not governed only by the accuracy of a report or the visual quality of a dashboard. It is also governed by whether data was collected lawfully, transformed transparently, protected appropriately, retained only as long as necessary, and used for legitimate purposes. In Power BI, these responsibilities extend into Power Query, because Power Query connects to sources, imports or references data, applies transformations, and may expose sensitive information through query steps, previews, refresh processes, and dataflows.

Data governance is the coordinated exercise of authority, control, and decision-making over data assets. For a Power BI practitioner, governance is practical rather than abstract. It determines who may access a customer table, whether a personal identifier should be removed during ingestion, how a query should be named, how a transformation can be traced back to its source, and what evidence must be retained for an audit.

Security, privacy, ethics, and compliance overlap but are not identical. Security protects information against unauthorized access, alteration, loss, and disclosure. Privacy concerns the appropriate collection and use of information about identifiable people. Ethics addresses what ought to be done, including fairness and responsible use, even where an action is technically legal. Compliance concerns meeting applicable laws, regulations, contracts, and organizational policies.

This chapter presents a practical governance framework for Power Query workflows. It focuses on lineage, documentation, naming standards, least-privilege access, privacy classification, responsible data handling, auditability, retention, and regulatory principles.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain the relationship between data governance, security, privacy, ethics, and compliance.
2. Describe data lineage and document a Power Query workflow so that it is understandable and auditable.
3. Apply consistent naming and documentation standards to queries, parameters, steps, and outputs.
4. Use least-privilege principles and privacy classifications when designing access and transformations.
5. Identify responsible practices for handling personal, confidential, and regulated data.
6. Explain the role of audit trails, retention schedules, regulatory principles, and controlled change.
7. Evaluate a Power BI data workflow through a governance and risk perspective.

1. Governance as a Lifecycle Responsibility

Data governance applies throughout the data lifecycle:

1. Creation or collection: Data is generated by transactions, forms, sensors, applications, or external providers.
2. Acquisition: Data is connected to Power Query through files, databases, APIs, dataflows, or other systems.
3. Transformation: Data is cleaned, filtered, joined, grouped, reshaped, and enriched.
4. Storage and publication: Data is stored in semantic models, workspaces, dataflows, or reports.
5. Use and sharing: Users consume dashboards, exports, alerts, and analytical outputs.
6. Retention and disposal: Data is retained according to business, legal, and operational requirements, then securely deleted or anonymized.

A governance failure can occur at any stage. For example, a query may be technically correct but violate policy because it imports unnecessary national identification numbers. Alternatively, a secure source may become insecure when a report is exported to an uncontrolled spreadsheet.

Governance normally assigns responsibilities to several roles:

These roles may be held by different people or combined in a small organization, but the responsibilities should still be explicit.

2. Core Principles for Power Query Governance

Several principles provide a practical foundation.

Purpose limitation and data minimization

Collect and process data for a defined, legitimate purpose. Import only the fields and records required for that purpose. If a sales dashboard needs region and revenue, it generally does not need customer birth date or full address. Removing unnecessary sensitive columns early reduces exposure through previews, temporary files, refresh logs, and downstream exports.

Accuracy and quality

Data should be sufficiently accurate, complete, timely, and consistent for its intended use. A transformation that silently converts invalid dates to null values may hide a quality problem. A governed workflow records how exceptions are handled and, where appropriate, routes them to a quality review process.

Transparency and explainability

Users should be able to understand where a value came from and how it was changed. Power Query's applied steps provide useful technical evidence, but meaningful governance also requires business descriptions, source details, assumptions, and ownership information.

Security by design

Security should be considered before a query is published. Credentials must not be embedded in M code or stored in unsecured files. Connections should use approved authentication methods, and access should be limited to what the workflow requires.

Accountability

An organization should be able to demonstrate who approved a dataset, who changed a transformation, when a refresh occurred, and how an incident would be investigated. Accountability requires ownership, records, and repeatable controls.

3. Lineage and Documentation

Data lineage is the traceable record of a data element's movement and transformation from origin to final use. It may include source systems, tables, files, queries, transformation steps, data models, reports, and consumers.

Lineage can be considered at three levels:

• Source lineage: Where did the data originate?
• Transformation lineage: What operations changed it?
• Consumption lineage: Which models, reports, exports, or decisions use it?

For example, a revenue figure might be traced as follows:

ERP Sales.InvoiceLines → Power Query q_Sales_InvoiceLines → filtered posted invoices → joined product dimension → Sales semantic model → Executive Revenue Report

Power Query documentation should be sufficient for another competent practitioner to understand and reproduce the workflow. A useful query record includes:

• business purpose;
• owner and steward;
• source system and source object;
• connection type and refresh frequency;
• privacy and security classification;
• assumptions and exclusions;
• key transformations;
• expected data types and quality rules;
• dependencies and downstream outputs;
• error-handling approach;
• last review date and change history.

Documentation inside Power Query

Practical techniques include:

• using descriptive query names;
• grouping queries by function, such as 01_Staging, 02_Transform, and 03_Output;
• adding comments to complex M expressions;
• creating a Parameters group for approved configurable values;
• using query descriptions where available;
• disabling load for staging and helper queries when they are not intended for the model;
• recording business definitions in a separate data dictionary;
• documenting exceptional filters, such as excluding cancelled transactions.

Documentation should explain why a transformation exists, not only what it does. “Removed cancelled invoices because the reporting policy recognizes posted invoices only” is more useful than “Filtered rows.”

4. Naming Standards and Metadata

Naming standards reduce ambiguity and make lineage easier to interpret. An organization should define a convention before a project becomes large.

A possible convention is:

Names should be stable, meaningful, and readable. Avoid names such as Query1, FinalFinal, or New Source. Naming should also distinguish business meaning from technical implementation. For example, out_ActiveCustomers describes an output more clearly than Table2.

A naming standard should address:

• capitalization and separators;
• abbreviations;
• singular or plural nouns;
• date and time conventions;
• treatment of confidential fields;
• versioning and deprecated objects;
• names for parameters, functions, staging queries, and outputs.

Metadata is data about data. A data dictionary may define NetSales as revenue excluding tax, returns, and cancelled invoices, with currency and reporting period specified. Without such definitions, two reports may use the same label for different calculations.

5. Privacy Classification and Responsible Handling

Classification helps determine how data should be accessed, transformed, shared, and retained. Organizations may use different labels, but a practical scheme is:

Classification should be assigned according to the content and context. A name may be low risk in one dataset but sensitive when combined with medical information or financial records. This is known as the mosaic effect: individually modest data items can become highly revealing when combined.

Personal data in Power Query

Personal data may appear in source columns, file names, folder paths, error messages, sample previews, and exported results. Responsible practices include:

• remove or mask unnecessary identifiers as early as possible;
• avoid loading sensitive columns into the semantic model unless required;
• use pseudonymization where identity is not needed;
• restrict development access to realistic but non-production data where possible;
• prevent sensitive values from appearing in screenshots, sample files, or training materials;
• verify that joins do not unintentionally re-identify individuals;
• define procedures for access, correction, deletion, and incident response where applicable.

Pseudonymization replaces direct identifiers with codes, but it does not necessarily make data anonymous. If an organization retains the key or can reasonably reconnect the code to a person, the data may remain personal data under applicable law.

6. Least-Privilege Access and Security Controls

The least-privilege principle states that a user, process, or service should receive only the access necessary to perform an authorized task, for only as long as necessary. It applies to source systems, Power BI workspaces, gateways, dataflows, semantic models, files, and administrative functions.

A developer who needs to build a sales report may require read access to approved sales views, but not administrator access to the entire database. A refresh account should not automatically have permission to modify source records.

Important controls include:

• role-based access rather than individual exceptions where practical;
• separate development, test, and production environments;
• approved authentication, preferably managed identities or organizational accounts where supported;
• secure credential storage through platform-managed connections;
• multi-factor authentication for privileged access;
• workspace roles aligned with actual responsibilities;
• row-level security where users should see different records;
• restricted export, sharing, and download capabilities for sensitive content;
• periodic access reviews and removal of dormant accounts.

Power Query privacy settings and firewall behavior also matter. When a query combines sources, the organization should understand whether information could flow from one source to another and whether the combination is permitted. Source privacy levels are not a substitute for organizational classification, contractual controls, or database security; they are one technical component of a wider protection system.

7. Auditability and Controlled Change

An auditable workflow leaves reliable evidence of what happened. Useful evidence includes:

• query and model ownership;
• source and destination details;
• refresh history and failure records;
• approvals and access reviews;
• version-controlled M code or deployment artifacts where available;
• change descriptions and timestamps;
• data-quality exceptions;
• incident and remediation records.

A transformation should be changed through a controlled process. A basic change procedure is:

1. Describe the requested change and its business reason.
2. Assess privacy, security, quality, and downstream impact.
3. Test the change with representative data.
4. Obtain appropriate approval.
5. Deploy using the organization's release process.
6. Verify refresh, outputs, permissions, and lineage.
7. Record the result and communicate material changes.

Auditability does not mean retaining every piece of data forever. It means retaining appropriate evidence while respecting retention limits and privacy requirements.

8. Retention, Disposal, and Regulatory Principles

Retention is the period for which data or records are kept. A retention schedule should distinguish between:

• source data;
• transformed data;
• published reports and snapshots;
• audit logs;
• approvals and documentation;
• backups and disaster-recovery copies.

Retention should be based on legal obligations, contractual terms, operational needs, historical value, and risk. Keeping data “just in case” increases exposure and may conflict with storage limitation principles. Disposal should be documented and effective, including deletion from relevant workspaces, files, extracts, backups, and replicated locations where required.

Regulatory requirements differ by jurisdiction and sector. Common principles found in privacy frameworks include lawfulness, fairness, transparency, purpose limitation, data minimization, accuracy, storage limitation, integrity, confidentiality, and accountability. Examples of regulatory contexts include the General Data Protection Regulation (GDPR), the California Consumer Privacy Act (CCPA/CPRA), sector-specific health or financial rules, records-management requirements, and contractual data-processing obligations. These examples are not interchangeable, and legal advice may be required to determine applicability.

A Power BI practitioner should not assume that a platform feature automatically satisfies a regulation. Compliance depends on the complete organizational arrangement: purpose, notices, contracts, access, controls, incident response, retention, and evidence.

9. Ethics and Responsible Analytics

Ethical data handling asks whether an analytical use is fair, proportionate, transparent, and respectful. A technically lawful workflow can still be harmful. Ethical risks include:

• using data for a purpose that people would not reasonably expect;
• excluding groups because of biased or incomplete data;
• using proxy variables that reproduce discrimination;
• presenting uncertain results as precise facts;
• monitoring employees excessively;
• allowing automated recommendations to make consequential decisions without human review.

Power Query transformations can contribute to bias. Filtering out records with missing demographic values may produce a cleaner table but systematically remove particular groups. Joining datasets with different geographic definitions may make comparisons appear valid when they are not. Aggregating small groups may also enable re-identification.

Responsible practice includes documenting limitations, testing quality across relevant groups, obtaining review for high-impact uses, minimizing sensitive attributes, and ensuring that decision-makers understand that a dashboard is evidence rather than unquestionable truth.

10. Applied Case: Regional Retail Performance Model

The following is an illustrative case.

Background

A retail organization wants a Power BI dashboard showing monthly sales, returns, customer segments, and store performance. Data comes from an enterprise resource planning system, a customer relationship management system, and spreadsheets maintained by regional managers.

Problem

The initial prototype imports full customer names, email addresses, postal addresses, and loyalty numbers. It also uses manually maintained spreadsheets with inconsistent store names. Several analysts have workspace administrator access, and no one has documented which figures exclude cancelled transactions. The organization cannot explain why the dashboard's customer count differs from the CRM report.

Analysis

The governance review identifies five risks:

1. unnecessary personal data is loaded into the model;
2. inconsistent store names cause failed joins and duplicate locations;
3. broad access increases the chance of unauthorized disclosure;
4. undocumented filters reduce trust and auditability;
5. the source and transformation lineage is unclear.

Approach and solution

The team assigns a business data owner and a technical steward. It classifies customer contact fields as personal and removes them from the reporting output. A stable customer key is retained only where required for authorized aggregation, while direct identifiers are excluded.

Queries are reorganized as follows:

• stg_ERP_SalesLines imports approved sales fields;
• stg_CRM_Customer imports the minimum customer attributes needed;
• stg_StoreReference imports the controlled store mapping;
• trn_SalesClean standardizes data types and excludes cancelled transactions;
• out_SalesMonthly produces the reporting table.

A controlled store reference table maps spelling variants to one approved store identifier. A data dictionary defines net sales and customer count. Developers receive contributor access only in the development workspace, while production deployment is restricted to authorized release personnel. Refresh credentials are stored using approved connection management rather than in files or code.

The team records source owners, refresh schedules, transformation reasons, classification, retention requirements, and report dependencies. A test compares record counts and totals before and after transformation, and exceptions are logged rather than silently discarded.

Outcome

The dashboard contains less personal information, produces more consistent store results, and can be explained to internal reviewers. Access is aligned with job responsibilities, and the organization can identify the source and purpose of each published metric. The project is not “risk free,” but risks are visible, assigned, and controlled.

Learning points

The case demonstrates that governance is not a final approval step. It shapes source selection, query design, naming, access, testing, publication, and retirement. Minimization and documentation often improve both privacy and analytical quality.

11. Governance Checklist for Power Query Workflows

Before publishing or materially changing a workflow, ask:

Purpose and ownership

• Is the business purpose documented?
• Is there an accountable owner and technical steward?
• Are downstream consumers known?

Source and lineage

• Are sources approved and traceable?
• Are transformations and exclusions documented?
• Can a metric be traced from report to source?

Privacy and security

• Has data been classified?
• Are unnecessary personal or sensitive fields removed early?
• Are credentials protected and access limited?
• Are exports, sharing, and row-level access considered?

Quality and change

• Are data types, duplicates, missing values, and exceptions tested?
• Is the workflow separated into appropriate staging, transformation, and output layers?
• Is there a controlled approval and deployment process?

Retention and compliance

• Is the retention period defined?
• Is disposal possible and documented?
• Have relevant legal, contractual, and sector requirements been reviewed?

12. Limitations and Contemporary Developments

No governance framework eliminates risk. Lineage tools may not capture manually exported files or informal downstream analysis. Classification can become outdated as datasets are combined. Access controls can fail through credential sharing or excessive permissions. Documentation may also become inaccurate when workflows change without review.

Cloud platforms increasingly provide centralized catalogs, sensitivity labels, impact analysis, endorsement, deployment pipelines, and activity logs. These features can strengthen governance, but they require correct configuration and active ownership. Artificial intelligence introduces additional concerns: prompts and outputs may contain confidential information, generated transformations may be incorrect, and automated classification may mislabel data. AI-assisted development should therefore be reviewed by a competent person, tested against approved data, and governed by organizational policy.

The strongest approach combines people, process, and technology. A label without enforcement is weak; a security control without ownership is neglected; documentation without review becomes obsolete.

Chapter Summary

Data governance provides the authority and controls needed to manage data responsibly across its lifecycle. In Power Query, governance is visible in source selection, data minimization, transformations, naming, documentation, access, refresh, publication, retention, and disposal.

Data lineage connects sources, transformations, models, reports, and users. Good documentation records both technical operations and business reasons. Naming standards and metadata reduce ambiguity. Least-privilege access limits exposure by granting only necessary permissions. Privacy classification guides handling requirements, while responsible practices remove unnecessary identifiers and consider re-identification and ethical risks.

Auditability depends on ownership, change records, refresh evidence, approvals, and quality testing. Retention should be purposeful and disposal should be controlled. Regulatory principles commonly emphasize lawful and transparent processing, purpose limitation, minimization, accuracy, storage limitation, confidentiality, and accountability, but applicability depends on jurisdiction and context. Finally, governance is a continuous discipline: tools assist it, but accountable people and repeatable processes make it effective.

Key Terms

Data governance: Coordinated authority, control, and decision-making over data.

Data lineage: A record of data origins, transformations, dependencies, and uses.

Data steward: A person responsible for data definitions, quality, and operational guidance.

Data owner: The person accountable for permissible use, access, and business decisions about a data asset.

Data custodian: The person or team responsible for technical operation and protection of data systems.

Data minimization: Collecting and processing only the data necessary for a defined purpose.

Purpose limitation: Using data only for specified, legitimate, and compatible purposes.

Privacy classification: Categorizing data according to sensitivity and required protections.

Personal data: Information relating to an identified or identifiable individual.

Pseudonymization: Replacing direct identifiers with codes while retaining a possibility of re-linking.

Anonymization: Processing data so that individuals are not reasonably identifiable.

Least privilege: Granting only the minimum access necessary for an authorized task.

Auditability: The ability to provide reliable evidence of actions, changes, ownership, and decisions.

Retention: The controlled period for which data or records are kept.

Data dictionary: A structured description of fields, definitions, meanings, and rules.

Metadata: Information that describes data and its context.

Row-level security: A control that restricts which rows a user can view.

Responsible analytics: Analytical practice that considers fairness, transparency, privacy, limitations, and human impact.

Review Questions

5 Short Answer Questions

1. What is data lineage, and why is it useful in a Power Query workflow?
2. Define the least-privilege principle.
3. Why should unnecessary personal data be removed early in a workflow?
4. Distinguish a data owner from a data steward.
5. What is the purpose of a data dictionary?

5 Long Answer Questions

1. Explain how governance applies across the data lifecycle from collection to disposal.
2. Discuss the relationship and differences between security, privacy, ethics, and compliance.
3. Describe a suitable documentation and naming standard for a multi-query Power BI solution.
4. Explain how auditability and controlled change improve trust in published reports.
5. Discuss why retention and disposal are important components of responsible data management.

3 Analytical / Application Questions

1. A customer-service dashboard imports names, email addresses, complaint details, and purchase history, although managers only need monthly complaint counts by region. Evaluate the risks and propose a redesigned workflow.
2. A report combines an ERP database with regional spreadsheets containing inconsistent store names. Design a governance and quality approach that improves lineage, standardization, testing, and accountability.
3. An analyst requests administrator access to a production workspace to refresh a report. Assess the request using least privilege, separation of duties, auditability, and operational practicality.

Multiple Choice Questions with Answers

Question 1

Which statement best describes data lineage?

A. A list of users who can open a report

B. A record of data origins, transformations, dependencies, and uses

C. A schedule for deleting old records

D. A method for encrypting database passwords

Correct Answer: B. A record of data origins, transformations, dependencies, and uses

Question 2

What does data minimization require?

A. Importing every available field for future analysis

B. Keeping all source records indefinitely

C. Processing only the data necessary for a defined purpose

D. Replacing all numeric fields with text

Correct Answer: C. Processing only the data necessary for a defined purpose

Question 3

Which query name best follows the naming examples in the chapter for a final monthly sales output?

A. Query1

B. FinalFinal

C. out_SalesMonthly

D. Table2

Correct Answer: C. out_SalesMonthly

Question 4

Which practice reflects least privilege?

A. Giving every developer administrator access

B. Giving a refresh account only the permissions needed to read approved source data

C. Sharing one administrator password with the whole team

D. Allowing all users to export sensitive data

Correct Answer: B. Giving a refresh account only the permissions needed to read approved source data

Question 5

Why is pseudonymized data not necessarily anonymous?

A. It always contains incorrect values

B. It cannot be stored in a database

C. The organization may still be able to reconnect codes to individuals

D. It contains no identifiers of any kind

Correct Answer: C. The organization may still be able to reconnect codes to individuals

Question 6

Which item is most appropriate for a Power Query workflow record?

A. The developer's personal password

B. The business reason for an important filtering step

C. An unapproved copy of all customer records

D. A list of unrelated websites

Correct Answer: B. The business reason for an important filtering step

Question 7

Which principle concerns using data only for specified and legitimate purposes?

A. Purpose limitation

B. Least-cost routing

C. Interface inheritance

D. Randomization

Correct Answer: A. Purpose limitation

Question 8

Which action best improves auditability when changing a production query?

A. Change it without recording the reason

B. Replace the query and delete previous evidence

C. Assess impact, test, obtain approval, deploy, verify, and record the change

D. Give all users permission to edit it

Correct Answer: C. Assess impact, test, obtain approval, deploy, verify, and record the change

Question 9

A dashboard excludes records with missing demographic values and is then used to compare groups. What is a principal ethical concern?

A. The query may create or amplify bias by systematically excluding groups

B. The report will necessarily refresh faster

C. The database will become encrypted automatically

D. The data will automatically become anonymous

Correct Answer: A. The query may create or amplify bias by systematically excluding groups

Question 10

What should a retention schedule primarily define?

A. Only the colors used in a dashboard

B. How long relevant data and records are kept and how they are disposed of

C. The number of visuals on every report page

D. The names of all computer monitors

Correct Answer: B. How long relevant data and records are kept and how they are disposed of