pivot table external source

[MetLife]

Hi,

I want to create a pivot table in excel, using an external .csv file. To do this I select: PivotTable-->From External Data Source-->Choose connection-->Browse for More--> select the .csv file --> then I select "deliminted" to import --> Click OK

The error message = "The type of connection selected cannot be used to create a PivotTable".

Is there a way to do this?

Thanks,

 

[alansidman]

I would import the data to Power Query and close and load to a Pivot Table.
Power Query is found on the Data Tab of the Ribbon and is known as "Get and Transform Data"
Select Data
Get and Transform Data
From CSV
Transform
Close and Load
Select Pivot Table.

 

[MetLife]

I would import the data to Power Query and close and load to a Pivot Table.
Power Query is found on the Data Tab of the Ribbon and is known as "Get and Transform Data"
Select Data
Get and Transform Data
From CSV
Transform
Close and Load
Select Pivot Table.

Do I go to Data-> Power Pivot For Excel? Then import as a csv

Or

Get Data -> File?

 

[alansidman]

Do I go to Data-> Power Pivot For Excel? Then import as a csv

I just tried doing that and it also worked. Open PP and Add to the Data Model. Add a text file.

FYI--In my post, I did not use PP, but Power Query. You now have two options.

 

[MetLife]

I just tried doing that and it also worked. Open PP and Add to the Data Model. Add a text file.

FYI--In my post, I did not use PP, but Power Query. You now have two options.

What is the advantage of doing this vs. copying the table into excel? When I use this "PP" the size of the workbook increases in MB by the same amount as the external table. So it looks like excel is saving the data within the worksheet.

 

[alansidman]

Personal Preference and comfort in the work environment.
Also, if you make changes to the source data and refresh the PT, it updates automatically.