How to extract values from list of records in Power BI?

by

User Question: “I have a column in my Power BI table that contains lists of values, say for example, a list of product categories. How can I extract these values into separate rows so that i can analyze my dataset?

In Power BI, you often encounter data where multiple values are stored in a single cell, typically as a list or delimited string. This tutorial will guide you through the process of extracting these values into separate rows, enhancing your data model for more flexible analysis.

We will assume the following data model:

  • Table: Sales
  • Columns: OrderID (Text), CustomerName (Text), OrderDate (Date), TotalAmount (Decimal), ProductCategories (Text)

Step-by-Step Instructions

  1. Load your data into Power BI Desktop.
  2. Select the table containing the list column (in this case, “Sales”).
  3. Open the Power Query Editor by clicking the Transform data button.
  1. Select the column with the list of values (ProductCategories).
  2. Go to the “Transform” tab and click on “Split Column” > “By Delimiter”.
  3. Choose the appropriate delimiter (e.g., comma) and select “Advanced options”.
  4. Check “Split into rows” and click “OK”.
  1. Rename the new column to “ProductCategory”.
  1. Click “Close & Apply” to apply the changes and return to the report view.

M Code for the Transformation

let
    Source = Excel.Workbook(File.Contents("C:\Sales_Data.xlsx"), null, true),
    Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
    SplitCategories = Table.ExpandListColumn(Table.TransformColumns(Sales_Table, {{"ProductCategories", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ProductCategories")
in
    SplitCategories

Real-world implementation ideas (HR focused)

Imagine an HR dataset where each employee record includes a list of skills. By extracting these skills into separate rows, you can:

  1. Create a visual showing the most common skills across departments.
  2. Filter reports based on specific skills.
  3. Analyze skill distribution and identify skill gaps more effectively.

Key Concepts and Reasoning

  • List Extraction: This process transforms a single row with multiple values into multiple rows, each with a single value.
  • Data Granularity: Increasing data granularity allows for more detailed analysis and flexible reporting.
  • Power Query: Utilizing Power Query for this transformation ensures the process is repeatable and updates with data refreshes.

Troubleshooting

  • Inconsistent Delimiters: If your data uses inconsistent delimiters (e.g., sometimes commas, sometimes semicolons), use the Splitter.SplitTextByDelimiter function with multiple delimiters.
  • Blank Values: After splitting, you might encounter blank values. Use the “Remove Empty” option in Power Query to clean your data.
  • Duplicates: Splitting rows may create duplicates in other columns. Use “Remove Duplicates” on relevant columns if needed.

Remember! – You might need to adjust your data model relationships after this transformation to ensure accurate analysis across your expanded dataset.