PowerBi is smart but also lazy

Introduction

For the past few days I have been doing data cleaning using PowerBi 34 columns to be precise.Data cleaning and transformation to me always seemed like a light task compared to other data related tasks.To my surprise it was the most obvious simple-complicated task after I experienced two stack overflow errors during cleaning.PowerBi was unresponsive which means closing it and all the cleaned data was gone and back to square one cleaning again.I felt like a data janitor literally. With that it made me dive into this "stack overflow error" to avoid this kind of problems again.

What is stack overflow first?

Stack overflow:Its when a program has exceeded memory usage on the call stack when too many functions are called without returning the required output which causes the stack to fill up and overflow.

Stack overflow error in PowerBi

A stack overflow error in powerBi occurs when there is an infinity loop. In this case PowerBi is trapped looking for instance a calculation e.g Calculation B is looking at Calculation C and C is also looking for B to get an output. This will force PowerBi to crash to Protect itself since its in a infinite loop that will eventually lead to the overflow.

What causes this?

With this case it happened during Data transformation in Power Query.

• In a query a step might be self referencing itself
  step 1=step 1
  This causes PowerBi to evaluate the dependency between the two until it overflows since it keeps referencing to the same thing leading to an endless loop

  • Complex nested Transformations in excess: In my PowerBi,I had close to 300 chain of transformations and this lead to the creation of a deep expression tree. This was caused by:
  • A lot of nested transformation functions i.e Table.Transform columns()
  • Repeated Replace value functions i.e Table.Replace value()
  • Deep nested if...and...elsestatements which leads to formation of a huge expression that is difficult for Power Query to evaluate.

How do we fix it?

1. Loading the entire data you are working on directly to your local memory(RAM) of your computer using the Table.Buffer() function.[This is very important since Power Query operates in a "lazy-smart" way through "lazy evaluation" which means it does not process data until you click Apply. If you were in a deep expression tree like me Power Query crashes and you lose all the transformed data.Quite frustrations there]
2. During data transformation you can decide to break it into smaller executable queries.

The Table.Buffer() function;

After finding out about this function and what it does, I had to dig deeper and see what else it can do and found some interesting areas it can be used:

• Before performing a complex Join i.e Merging a large table into another large table, the Table.Buffer(Table_Name) function will help in speeding up the merging process.

• In web scraping or pulling from a stubborn API the function will help Power Query hit the web sources at once and locks the data.Its very important since you will avoid spamming the API with requests every time.