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

 

[RoryA]

You’ll need your query to include a field like the file name so that you can distinguish between the two.

 

[Jyggalag]

You’ll need your query to include a field like the file name so that you can distinguish between the two.

Dear Rory,

Thank you so much! Can you elaborate on this please? Perhaps with an example

Kind regards,
Jyggalag

 

[Jyggalag]

You’ll need your query to include a field like the file name so that you can distinguish between the two.

Right now I only have two data files (plan to get 100+ in the future), they have these names:

So the files where I am getting my data from in the query are unique in their names, with one being the 1st and the other being denoted by the "2.0"

Is this what you were referring to?

BR
Jyggalag

 

[RoryA]

Yes. You need something you can put in the Column fields area of the pivot to distinguish the data.

 

[Jyggalag]

Yes. You need something you can put in the Column fields area of the pivot to distinguish the data.

Ah okay,

My issue is that I download this data every week and it would be nice to simply just: 1) download it, 2) place it in the folder, 3) go into the excel file and press "Refresh" to see the updated data in a new column next to the old data.

Is this possible? Or would I have to go into the new file every week and type "Column 1, January 21st" and then "Column 1, January 28th" the next week and so forth?

Thank you Rory! I really appreciate your time, this issue has been bugging me all week

 

[RoryA]

You can't keep the old data unless you store it somewhere. PQ will only import whatever data is in the folder - it will not keep the old data for you.

 

[Jyggalag]

You can't keep the old data unless you store it somewhere. PQ will only import whatever data is in the folder - it will not keep the old data for you.

Yes but I plan to have one folder where I import all my data to.

So week 1 I will import the data sheet for week 1 and the folder will contain "Week 1 data"
Week 2 I will import the data sheet for week 2 and the folder will contain "Week 1 data & Week 2 data"
Week 3 I will import the data sheet for week 3 and the folder will contain "Week 1 data, Week 2 data & Week 3 data".

Please keep me updated

 

[RoryA]

That's fine then, but you will need to include something in your query that can differentiate the different data sources. Typically I would use the file name, or some part thereof.

 

[Jyggalag]

Z

That's fine then, but you will need to include something in your query that can differentiate the different data sources. Typically I would use the file name, or some part thereof.

Yes, that's what I did as well, like I mentioned before, so my files have these names:

So 99% similar names, except the one for week 2 has "2.0" to differentiate it from the other one.

However, I am still unable to setup my pivot table in the way that i want (as stated in the original thread statement), and I also don't know how I would go about doing it haha

Am I sourcing correctly? And what should my next step be?

Thank you Rory!