# Transform Rows to columns



## Myura (Jun 13, 2016)

Hi!
Please help me with my thesis research. I have downloaded huge data set to analyse. 
For the first step, I want to transform the data. I want dates to be in the rows, and company names to be in the columns. 
Currently it is like this;

Name             Date                 Price
Company A     15/05/2015         3.5
Company A     16/05/2015         4.5
Company B     15/05/2015         3.5
Company B     16/05/2015         4.5


Mush appreciated for your help


----------



## sheetspread (Jun 13, 2016)

Like in the regular pivot table, put the date field in columns, companies in rows, price in values.


----------



## Myura (Jun 13, 2016)

Thank you for your reply. But unfortunately I'm new to power BI. I just learned to upload the csv file. Could you please guide me with steps to achieve it. It would be much appreciated.
Thanks again.


----------



## sheetspread (Jun 13, 2016)

Here's how to set up the table:

https://www.youtube.com/watch?v=HCPRegwjtVE

But follow what I wrote above when arranging the fields.


----------



## Myura (Jun 13, 2016)

Thank you so much. I was helpless until I found this Forum. And you are a star sheetspread.


----------



## Myura (Jun 13, 2016)

sheetspread said:


> Here's how to set up the table:
> 
> https://www.youtube.com/watch?v=HCPRegwjtVE
> 
> But follow what I wrote above when arranging the fields.



I could not find the button for power pivot in Power BI.


----------



## Drrellik (Jun 13, 2016)

I think the short cut keys for PT are ALT+D, P


----------



## Myura (Jun 13, 2016)

I tried that short cut in the PowerBI desktop, and it is not working. I tried as the video shows in Excel, but my data is too large, so it cannot transform all rows to columns. Thats why I thought Power BI could be a solution for this.


----------



## sheetspread (Jun 13, 2016)

Myura said:


> I could not find the button for power pivot in Power BI.



It should be in the powerpivot ribbon, are you seeing that above? If not, you can set it through the file menu. I've only tried the shortcut that Drrellik posted with regular pivot tables, but see if it works (Alt-D-P).


----------



## sheetspread (Jun 13, 2016)

Myura said:


> I tried that short cut in the PowerBI desktop, and it is not working. I tried as the video shows in Excel, but my data is too large, so it cannot transform all rows to columns. Thats why I thought Power BI could be a solution for this.




How many rows is your data?


----------



## Myura (Jun 13, 2016)

Hi!
Please help me with my thesis research. I have downloaded huge data set to analyse. 
For the first step, I want to transform the data. I want dates to be in the rows, and company names to be in the columns. 
Currently it is like this;

Name             Date                 Price
Company A     15/05/2015         3.5
Company A     16/05/2015         4.5
Company B     15/05/2015         3.5
Company B     16/05/2015         4.5


Mush appreciated for your help


----------



## Myura (Jun 13, 2016)

sheetspread said:


> How many rows is your data?



more than 3 million. But it will be less as we group them in company names.

currently it is like this
Name Date Price
Company A 15/05/2015 3.5
Company A 16/05/2015 4.5
Company B 15/05/2015 3.5
Company B 16/05/2015 4.5

I want it to be
--------------------Company A-----company B-------company C
15/05/2015-------8.85--------------8.98-----------------9.87
16/05/2015-------8.98--------------8.69-----------------10.98

Thank you


----------



## Drrellik (Jun 13, 2016)

Is this something you can just transpose the first column? 

insert (A) row.  at the top highlight A2:A?? copy and then select C1 right click paste special Transpose. 

not sure how much data you have.


----------



## sheetspread (Jun 13, 2016)

(I should've said in my first post companies go in columns, dates in rows, price in values but you probably figured that out)

If you're not grouping the dates (e.g. 1/2/16,1/3/16,1/14/16 would all be combined into January 2016) try this:

https://www.youtube.com/watch?v=lXdvrJ9K6F4


----------



## Myura (Jun 13, 2016)

Drrellik said:


> Is this something you can just transpose the first column?
> 
> insert (A) row.  at the top highlight A2:A?? copy and then select C1 right click paste special Transpose.
> 
> not sure how much data you have.



I do not thik it will work. I have 3 million rows. so I cannot perform this on excel itself.

currently it is like this
Name Date Price
Company A 15/05/2015 3.5
Company A 16/05/2015 4.5
Company B 15/05/2015 3.5
Company B 16/05/2015 4.5

I want it to be
--------------------Company A-----company B-------company C
15/05/2015-------8.85--------------8.98-----------------9.87
16/05/2015-------8.98--------------8.69-----------------10.98

Thank you


----------



## Drrellik (Jun 13, 2016)

WOW that is a lot of row's excel 2013 can only handle just over 1 million rows and a Pivot table that big would choke my little machine.....


----------



## Myura (Jun 13, 2016)

sheetspread said:


> (I should've said in my first post companies go in columns, dates in rows, price in values but you probably figured that out)
> 
> If you're not grouping the dates (e.g. 1/2/16,1/3/16,1/14/16 would all be combined into January 2016) try this:
> 
> https://www.youtube.com/watch?v=lXdvrJ9K6F4



I have tried the same. but error message saying "The pivottable report will not fit the sheet".


----------



## Drrellik (Jun 13, 2016)

Keep It Simple and Fast: Power Pivot: The PivotTable report will not fit on sheet.


----------



## sheetspread (Jun 13, 2016)

Myura said:


> I have tried the same. but error message saying "The pivottable report will not fit the sheet".



How many columns (companies) are there?


----------



## ImkeF (Jun 13, 2016)

In the query editor in PBI desktop or Power Query you can pivot large data like described here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/
Check the options in the "Aggregate Values" - you might want to choose "no aggregation".


----------



## Myura (Jun 13, 2016)

sheetspread said:


> How many columns (companies) are there?



around 96,000 companies. I know it is big *** annoying data set.


----------



## Myura (Jun 13, 2016)

Hi!
Please help me with my thesis research. I have downloaded huge data set to analyse. 
For the first step, I want to transform the data. I want dates to be in the rows, and company names to be in the columns. 
Currently it is like this;

Name             Date                 Price
Company A     15/05/2015         3.5
Company A     16/05/2015         4.5
Company B     15/05/2015         3.5
Company B     16/05/2015         4.5


Mush appreciated for your help


----------



## sheetspread (Jun 13, 2016)

Powerpivot and Power Query can definitely do this, even up to 100 million rows and several million columnns (which I'm told is very slow). How far exactly did you get with Powerpivot?


even though you must be tired of all these links, this one shows you how to get the csvs into powerpivot:

http://www.quepublishing.com/articles/article.aspx?p=1613314&seqNum=2


----------



## Myura (Jun 13, 2016)

sheetspread said:


> Powerpivot and Power Query can definitely do this, even up to 100 million rows and several million columnns (which I'm told is very slow). How far exactly did you get with Powerpivot?
> 
> 
> even though you must be tired of all these links, this one shows you how to get the csvs into powerpivot:
> ...



Thank you so much for your help. I'm not in the state to get tired .
76,519 companies got in to one sheet.


----------



## sheetspread (Jun 13, 2016)

https://www.youtube.com/watch?v=ms149Qi2PVA

Bill imports 7 million rows here (and this was the 2010 version)


----------



## Myura (Jun 13, 2016)

sheetspread said:


> https://www.youtube.com/watch?v=ms149Qi2PVA
> 
> Bill imports 7 million rows here (and this was the 2010 version)



He is showing the table in data model, not in the sheet. when we need the pivot table, it goes to sheet where we have restrictions.


----------



## Myura (Jun 13, 2016)

ImkeF said:


> In the query editor in PBI desktop or Power Query you can pivot large data like described here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/
> Check the options in the "Aggregate Values" - you might want to choose "no aggregation".



Thank you so much for your help. You saved my day. It worked. Thanks again.


----------



## Myura (Jun 13, 2016)

sheetspread said:


> https://www.youtube.com/watch?v=ms149Qi2PVA
> 
> Bill imports 7 million rows here (and this was the 2010 version)



I have managed to do it as *ImkeF **intructed. Thank you so much for your help. You are a champ. *


----------



## Myura (Jun 13, 2016)

ImkeF said:


> In the query editor in PBI desktop or Power Query you can pivot large data like described here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/
> Check the options in the "Aggregate Values" - you might want to choose "no aggregation".



I have managed to transform it in the Queries panel. But it cannot be used in the Power Bi tool as it can only allow 16,000 columns, mine is way too far from this.


----------



## sheetspread (Jun 13, 2016)

Why aren't you putting the companies in rows?


----------



## ImkeF (Jun 13, 2016)

Yes, the question is what you want to achieve with your query at all. 
Why the unpivot-step? That many columns will be hard to handle everywhere.


----------



## Myura (Jun 13, 2016)

sheetspread said:


> Why aren't you putting the companies in rows?



Yeah. May be I should formulate my research in other form. I will try and see. Thank you so much for all your help.


----------



## Myura (Jun 13, 2016)

Hi!
Please help me with my thesis research. I have downloaded huge data set to analyse. 
For the first step, I want to transform the data. I want dates to be in the rows, and company names to be in the columns. 
Currently it is like this;

Name             Date                 Price
Company A     15/05/2015         3.5
Company A     16/05/2015         4.5
Company B     15/05/2015         3.5
Company B     16/05/2015         4.5


Mush appreciated for your help


----------



## Myura (Jun 13, 2016)

ImkeF said:


> Yes, the question is what you want to achieve with your query at all.
> Why the unpivot-step? That many columns will be hard to handle everywhere.



I'm going to try to keep companies in rows and formulate my other variables to adjust this form. Thank you so much for all your help.


----------



## sheetspread (Jun 13, 2016)

Companies in rows, dates in columns. With grouping and the value field setting you can show monthly or yearly average prices, that will reduce the column size by a lot. Unless you must show each day, which to me seems excessive.


----------

