Problem with transformation in Power Query

[Anonymous]

Hi,

I'm the beginner user of Power BI and I need help in optimisation of Power Query transformations as these arised to be so heavy that don’t allow for smooth work.

As a source I have an Excel file with market data which I download from supplier system. The data looks as follows:

• First 4 columns contain descriptive data like: subregions (505), markets (11), brands (110) and product (212). Such combination gives approximately 107k rows.
• Next 28 columns contain volumes for last 28 months. At the end there are next 28 columns containing values also for same last 28 months.
• So the total table is 60 columns x 107k rows

I could download the data in expected layout (where years and months are included in two columns as variables and volumes and values in two separated columns) but unfortunately such layout exceeds 1 million rows.

Format of text file or Excel (the only possible options) don’t allow for such range.

Connection directly to supplier system is not an option.

Therefore to achieve expected layout in Power Query I applied below steps:

1. I unpivot columns with volumes and values. Result – above mentioned columns with descriptive data + column with values like “Vol 2021 01” + column with numbers
2. This column I splitted to “Vol / Val”, year and month columns
3. At the end I used “Pivot Column” function to transform “Vol / Val” column into to separate columns

This last step is the most overloading for memory and takes several dozen of minutes. Sometimes process of calculation / conversion of mentioned data simply don’t finish.

Do I have to reconsider Azure SQL to store transformed data?

Maybe the DAX Studio allows for more optimized transformation?

Any other solutions are welcomed 🙂

[mahoneypat]

Hard to troubleshoot w/o seeing your data and query.  But here is one article that show another way to deal with the vol and val columns instead of unpivoting/repivoting.  You might be able to adapt the Repeating Columns example, but it requires some basic M knowledge of creating Records and Lists.

Faster Data Transformations with List/Record M Functions - YouTube

Pat

[Anonymous]

Hi Pat,

I really apologise that I'm responding so late.

It's because I found some solution on differet forum, which met my expectations in most.

Nevertheless I'll also look at Yours carefully.

By the way I wanted to paste here some sample table as You wished but I faced an error with HTML.

[Icey]

Hi @Anonymous ,

---

It's because I found some solution on differet forum, which met my expectations in most.

---

Could you share us the solutions?

---

By the way I wanted to paste here some sample table as You wished but I faced an error with HTML.

---

You can refer to this post to provide sample data: How to provide sample data in the Power BI Forum - Microsoft Power BI Community.

Best Regards,

Icey

[Icey]

Hi @Anonymous ,

Any sharing?

Best Regards,

Icey