Pivot column function timeout

[tgjones43]

Hi all

I have a table of 4 columns and about 9 million rows. I have applied a pivot column function which should create 9 columns from the attribute column but it is just keeps running without completion. In PBI Desktop I eventually get a message saying run out of disk space, although I think this is perhaps more to do with how PBI is hosted on my company's IT platform. When I recreate the data model in Dataflows after 2 hours of processing it comes up with a message saying "Evaluation failure". If I filter the attribute column to keep just 1 of the 9 values the pivot column function works and loads data in a couple of minutes. It does that for any of the 9 options.

So is it issue that it cannot pivot such a large amount of data?

Thanks

Tim

[v-jingzhang]

Hi @tgjones43 

It may due to the large data size. Is it possible to apply some aggregations on the table in data source in advance? Or reduce unnecessary columns or rows to reduce the data size?

Best Regards,
Community Support Team _ Jing

[tgjones43]

Hi @v-jingzhang I think you're right that it is the large data size, it's just strange that this has happened all of a sudden after the query had been working fine. I have removed all unnecessary rows and columns already, so I will perhaps have to try something different.

[anadar]

I have 5500 rows, and 4 columns, still Pivot is timing out. Any other way we can fix this issue? It works very fast on Desktop, but not in PowerBi Service? I have a PPU license. 

Any other methods you can propose. 

[mahoneypat]

Usually unpivoting is the way to go. Are you sure you want to pivot your data?

In any case, how many columns do you have? Does it work if you first remove all but the Attribute and Value columns? If so, you could construct the query that way.

Pat

[tgjones43]

Hi @mahoneypat thank you for your reply. Yes I do need to pivot. Data is basically like this:

Category 1	Category 2	Attribute	Value
1	1	A	text
1	1	B	text
1	1	C	text
1	2	B	text
1	2	D	text

and needs to be like this:

Category 1	Category 2	A	B	C	D	E	F	G	H	I
1	1	text	text	text	null	null	null	null	null	null
1	2	null	text	null	text	null	null	null	null	null

final table should take up about 1.8 million rows. I do need both category columns.