Fixing Power BI Dataflow Error: “We Cannot Apply Field Access to the Type Text”

Introduction

If you’ve ever encountered the error message “Expression.Error: We cannot apply field access to the type Text” when refreshing a Power BI dataflow, you’re not alone. This error often prevents scheduled refreshes from running successfully, causing major disruptions in data processing.

Recently, I ran into this exact issue when working with SharePoint files. The problem? I needed to make sure a column always had 13 digits, even when the original values had fewer digits and needed leading zeros.

In this post, I’ll walk you through:

• The initial problem that caused the error.
• My original approach and why it failed.
• The corrected M code that fixed the issue.
• Other potential solutions and root causes.

Let’s dive in!

---

The Problem: Dataflow Refresh Failed

I was working with a Power BI dataflow that pulled data from a CSV file stored in SharePoint. My goal was simple:

👉 Make sure that every value in a specific column was exactly 13 digits long, padding shorter ones with leading zeros.

However, when I ran the dataflow refresh, it failed with the following error:

---

Dataflow Expression Error Message

Expression.Error: We cannot apply field access to the type Text.

This error blocked the entire scheduled refresh, making the dataset unusable.

---

My Original M Code (Before Fix)

Here’s the initial M code I wrote to pad the values to 13 digits:

// Ensure values are 13 Digits (Padding with Zeros)
PaddedColumn = Table.TransformColumns(#"Changed column type", {
    {"ColumnName", each Text.PadStart(Text.From([ColumnName]), 13, "0"), type text}
}),

This seemed correct at first glance, but Power BI threw an error. So, what went wrong?

---

Why Did This Fail?

The issue stems from how Table.TransformColumns processes each value.

• Problem 1: [ColumnName] inside the function refers to a record, not a value.
• Problem 2: Field access ([ColumnName]) does not work inside a row-wise function.
• Problem 3: The function expected a text value, but was being applied to something else.

---

The Fixed M Code

To resolve this, I needed to modify my approach slightly:

✅ Use the _ placeholder to reference the current row value.

Here’s the corrected version of the code:

// Ensure values are 13 Digits (Padding with Zeros)
PaddedColumn = Table.TransformColumns(#"Changed column type", {
    {"ColumnName", each Text.PadStart(Text.From(_), 13, "0"), type text}
}),

---

Why This Works

✔ _ correctly refers to the individual cell value inside the transformation. ✔ Text.From(_) ensures that any number is safely converted to text before padding. ✔ Text.PadStart(_, 13, "0") correctly applies leading zeros only when needed.

Once I implemented this fix, the dataflow refresh worked perfectly! 🎉

---

Other Potential Solutions & Root Causes

1. The Column is Already Text, but Contains Unexpected Characters

If the column already contains non-numeric characters (like spaces or special characters), Power BI might fail during transformations.

Fix: Try trimming and cleaning the column before applying transformations:

CleanedColumn = Table.TransformColumns(#"Changed column type", {
    {"ColumnName", each Text.Trim(Text.Clean(_)), type text}
}),

---

2. The Column Contains Null Values

If the column has null values, the Text.PadStart function will fail because null is not a valid text type.

Fix: Replace null values before applying transformations:

SafeColumn = Table.TransformColumns(#"Changed column type", {
    {"ColumnName", each if _ = null then "0000000000000" else Text.PadStart(Text.From(_), 13, "0"), type text}
}),

---

3. The Column is Stored as a Number Instead of Text

If the column is stored as a number, Power BI might try applying number-based transformations instead of treating it as text.

Fix: Ensure the column is explicitly converted to text:

ConvertedColumn = Table.TransformColumns(#"Changed column type", {
    {"ColumnName", each Text.PadStart(Text.From(Number.ToText(_)), 13, "0"), type text}
}),

---

How to Get the M-Code from Advanced Editor?

Step 1: Open the Advanced Editor
In Power BI Service, navigate to your dataflow, select the query you want to inspect, and click on “Edit Tables”. Once inside, click the “Advanced Editor” option to view and modify the M code directly.

Step 2: Debug the M Code
If you encounter errors like “We cannot apply field access to the type Text”, copy the entire M code and paste it into ChatGPT for debugging. Mention any recent column type changes, as data type mismatches (e.g., numbers stored as text or null values where a function expects a number) often cause refresh failures. Also, include details about your data source (e.g., SharePoint, SQL, or CSV) to help with troubleshooting.

Conclusion

The “Expression.Error: We cannot apply field access to the type Text” error can be frustrating, especially when your dataflow refresh fails without an obvious reason. In my case, it was caused by incorrectly accessing a column inside Table.TransformColumns.

---

Final Takeaways

✅ Use _ instead of [ColumnName] inside Table.TransformColumns. ✅ Always ensure the column is in the correct data type before applying transformations. ✅ Handle null values to prevent unexpected errors. ✅ If necessary, explicitly convert numbers to text before applying transformations.

🚀 Need more Power BI tips? Subscribe for updates!

I hope that helped!

---

FAQ’s: