Power BI: Get Name with Power Query

Key insights

• Name-Value custom fields from CRM or ERP systems often store attributes as pairs; use the PIVOT step in Power Query to transpose those pairs into a proper table for analysis.
  
• Follow a clear pipeline: Connect to the source, Transform the raw rows, Pivot the name column into columns, then Load the cleaned table into Power BI or Excel.
  
• Use Power Query in Power BI Desktop, Dataflow, Microsoft Fabric Data Factory, or Excel to apply the pivot consistently across environments for repeatable ETL.
  
• Efficient data transformation lets you turn messy custom-field outputs into analysis-ready tables; combine pivoted results with DAX and Power Pivot for richer modeling and measures.
  
• Practical tips: keep some columns static, choose the right aggregation for values, and enforce correct data types to avoid errors and duplicate keys when pivoting.
  
• Recent updates add smarter tools and better connectors, plus AI assistance like Copilot, making it easier to integrate diverse sources and automate common pivot steps.
  

Overview of the Video and Its Purpose

In a concise YouTube presentation, Reza Rad (RADACAD) [MVP] demonstrates how to extract structured data from a custom Name-Value field and prepare it for analysis in Power BI. The video focuses on a straightforward transformation in Power Query called PIVOT, which transposes name-value pairs into table columns. As a result, data that originally arrives in a compact, semi-structured form becomes usable in reports and models. This article summarizes the method, key takeaways, and practical tradeoffs for BI practitioners.

How the Pivot Transformation Works

First, Power Query lets you select the column that holds the names and the values and then pivot those names into distinct columns. Consequently, each name becomes a header and its corresponding values populate rows under that column, which turns scattered custom fields into a relational layout. The transformation typically requires choosing an aggregation method if multiple values map to the same pivot intersection, and the video shows simple choices to ensure predictable output. Therefore, the technique helps convert data from systems like CRM or ERP into a more analysis-ready schema.

Step-by-Step Demonstration Highlights

Reza Rad walks viewers through connecting to the source, cleaning unwanted columns, and selecting the key name and value fields before applying the PIVOT operation. Then, he emphasizes the importance of setting the proper aggregation and data types so that numbers remain numeric and dates remain dates after the pivot. Moreover, he points out that the same steps work across tools: in Power BI Desktop, in Dataflow, in Microsoft Fabric Data Factory, and even in Excel. As a result, teams can standardize the approach across deployment targets to ensure consistent models in warehouses and reports.

Tradeoffs When Choosing Pivot vs Alternatives

While pivoting simplifies analysis, it can also introduce sparsity when a dataset contains many unique names, which increases the column count and can bloat models. For instance, pivoting a large set of diverse custom fields may hurt performance and complicate downstream relationships, so you must balance convenience with model efficiency. Alternatively, keeping the data in a normalized long form preserves flexibility but adds the burden of extra joins or DAX logic for reporting. Consequently, teams must weigh the tradeoff between a wide, flat model that favors ease of visual creation and a long model that better supports scalability and storage efficiency.

Challenges and Practical Solutions

One common challenge is inconsistent naming in the source field, which leads to multiple near-duplicate columns after pivoting; therefore, standardizing keys before pivoting is essential. Additionally, the presence of nulls and mixed data types requires careful cleaning so that the pivot step does not force everything into text. Reza suggests simple preprocessing—such as trimming, case normalization, and type conversion—to reduce errors and speed up the pivot operation. In practice, these small steps often prevent the need to rebuild transformations later.

Implications for BI Workflows and Governance

Adopting a consistent pivot approach streamlines data import and improves report reliability, and it also supports reuse when implemented in shared Dataflow or Microsoft Fabric artifacts. However, governance matters: naming conventions, change control, and refresh strategies must align across teams to prevent breaking published reports. Moreover, when pivoting at scale, consider moving the transformation into the data warehouse layer if possible, because doing heavy transformation at report time can impact performance. Ultimately, the method shown in the video offers a practical balance between quick wins for analysts and the longer-term needs of enterprise data architecture.

Keywords

Power Query pivot name column, Power BI get data from name column, Pivot custom field Power BI, Extract custom field from name column Power Query, Power Query extract field from name column, Power BI pivot custom field into columns, Power Query split name column custom field, Power BI transform name column