PowerPivot / Power Query & Pivot Table??

[Jyggalag]

Dear all,

I have currently made a pivot table, which I am linking to some Power Query data.

The data in the power query comes from a folder where I import files from a website, so these files will ALWAYS have the same file name, sheet name and column names. However, the data under their columns will differ.

Right now I am putting "Type" under Filters in my pivot table to isolate for one type that i want to see. I am then putting "Questionnaire" and "Status" in Rows (in that order) and "Count of Questionnaire" in Values to show my questionnaires in a number format.

However, when I do the last part, the pivot table summarizes the count of questionnaires from file 1 and file 2 (the data files I use in Power Query) into, say, 85 in total summed in column B - while I actually want it to sum the data from the first file (could be 40) into column B and then create a new column (column C) and put the sum from data file 2 in here, which would then be 45.

I have no idea how to make Power Query and the pivot table do this and I have searched everywhere online and tried to ask questions everywhere, but have yet to find anybody who knows how to do this.

I truly believe that if you are good at Power Query / Pivot Table, then this should be easy, but I am completely new and have never used it before, so I would truly appreciate some assistance.

Thank you!

Kind regards,
Jyggalag

 

[Jyggalag]

No, I mean that in the field list for your pivot table, you should have Source.Name appearing. You need to use that in the Column field area.

No, I mean that in the field list for your pivot table, you should have Source.Name appearing. You need to use that in the Column field area.

Rory you absolute genius!!

I can't believe it, but it worked!!

THANK YOU SO SO MUCH!!! I literally cannot express how much this has helped me!

If I may ask a final question, do you have any idea how I can remove the grand total column?

 

[RoryA]

With any cell in the pivot table selected, you should have a pivot table tools group on the right of the ribbon. On the design tab, there are various grand total options you can select including None.

 

[Jyggalag]

With any cell in the pivot table selected, you should have a pivot table tools group on the right of the ribbon. On the design tab, there are various grand total options you can select including None.

Amazing! Thank you so much Rory