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 need to modify the query so that it brings in the file name as an additional column. Otherwise the pivot table has nothing to work with. As far as the pivot is concerned it's all one data set.

 

[Jyggalag]

You need to modify the query so that it brings in the file name as an additional column. Otherwise the pivot table has nothing to work with. As far as the pivot is concerned it's all one data set.

Okay, how do I do this?

And when I make a new file, do I then need to modify the pivot table and make it bring in this new file as well for a new column?

So it's not possible to use a folder with unique file names as sources in other to let the pivot table update and create new columns for every week automatically?

Sorry for the confusion. I am very new in this world Your guidance is really appreciated!

 

[RoryA]

No, there's no need to modify the pivot - just refreshing it will add any new file names as they appear in the data.

When you do a query from folder, one of the columns that is returned has the file name. You just need to make sure you keep that in the data set. If you can post your M code, I'm sure we can adapt it.

 

[Jyggalag]

No, there's no need to modify the pivot - just refreshing it will add any new file names as they appear in the data.

When you do a query from folder, one of the columns that is returned has the file name. You just need to make sure you keep that in the data set. If you can post your M code, I'm sure we can adapt it.

Dear Rory,

Thank you so much. I really appreciate how willing you are to help, you've brought a big smile to my face today That sounds amazing!

If I may ask, what is an M code and where do I find this?

When I look at my Power Query data I can currently see this:

I can pull in the two formulas here, if that is what you are referring to?

For the first source file it is: =

Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Overseeing Company", type text}, {"Delegate", type text}, {"Questionnaire", type text}, {"Type", type text}, {"Date Created", type date}, {"Initial Email", type date}, {"Reminder Email", type date}, {"Escalation Email", type date}, {"Status", type text}, {"Action", type any}})

And for the second source file it is = =

Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Overseeing Company", type text}, {"Delegate", type text}, {"Questionnaire", type text}, {"Type", type text}, {"Date Created", type date}, {"Initial Email", type date}, {"Reminder Email", type date}, {"Escalation Email", type date}, {"Status", type text}, {"Action", type any}})

Kind regards,
Jyggalag

 

[Jyggalag]

No, there's no need to modify the pivot - just refreshing it will add any new file names as they appear in the data.

When you do a query from folder, one of the columns that is returned has the file name. You just need to make sure you keep that in the data set. If you can post your M code, I'm sure we can adapt it.

Update:

The M code is actually the same for the two above, and they are taken from the FOLDER, not the files (sorry, my bad!).

I don't know why the folder appears twice, once with its name and a second time with its name followed by (2)

 

[RoryA]

Those are separate queries and what you have posted is merely one step in each. Select whichever query you are actually using for your pivot table source, then on the View tab, select Advanced Editor and you should see some code beginning with let. Copy and paste that here.

 

[Jyggalag]

Ah

Those are separate queries and what you have posted is merely one step in each. Select whichever query you are actually using for your pivot table source, then on the View tab, select Advanced Editor and you should see some code beginning with let. Copy and paste that here.

Ahh thank you so much!

I deleted the (2) now, no idea why it was there to begin with.

This is the full code:

let
    Source = Folder.Files("\\COMPANY.MSSS.COMPANY.NET\userdata\t5388334\home\Documents\PRIVATE 31-01-2022"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Overseeing Company", type text}, {"Delegate", type text}, {"Questionnaire", type text}, {"Type", type text}, {"Date Created", type date}, {"Initial Email", type date}, {"Reminder Email", type date}, {"Escalation Email", type date}, {"Status", type text}, {"Action", type any}})
in
    #"Changed Type"

 

[RoryA]

Then it looks like you already have the Source.Name field available to your pivot table. That needs to go into the column field area.

 

[Jyggalag]

Then it looks like you already have the Source.Name field available to your pivot table. That needs to go into the column field area.

Do you mean the part that says Source = Folder.Files?

And do I then edit this or?

 

[RoryA]

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.