Power Query: Combine and Transform from folder

[LUKE92]

I'm working on a project with power query to combine 18 historical P&L's into one table. I have simple mock-ups attached with a similar layout to my project, which is being built using an Essbase/Smart View Pull (accounts column on the left; year, month, and entity rows on top). Unfortunately, after using the folder as a data source and pivoting/unpivoting the corresponding columns, the values are returned as "error" instead of the actual value. I must be doing something wrong, but I'm new to Power Query and can't quite figure it out. Screen shots and mock-ups are attached below:

After importing folder as data source and extracting content, but before pivoting:
Power Query Before.png

After pivoting:
Power Query After.png

I know I'm getting close since I can get the Query into the layout that I'm looking for (filename in header), so I just need to get the values underneath those columns

Thanks for taking a look, and let me know if I'm missing something!

[AliGW]

Welcome to the forum!

How are you pivoting? Please provide the file containing the query.

What do you get if you click on the error - what is it telling you?

[AliGW]

Waiting for the extra information, Luke.

[LUKE92]

Hi Ali, and thanks for such a quick response! I apologize for the delay, but I had deleted the file and promptly forgot how I created it in the first place . I was able to recreate it following these steps:

1. Import folder to Query
2. Remove all columns except for content and name
3. Add custom column =Excel.Workbook([Content])
4. Expand workbookcontent column & include Name & data
5. Expand workbookcontent.data column. Include all columns
6. Remove Content column
7. Highlight first two columns (Name & WorkbookContent.Data.Column1, then unpivot other columns
8. Remove attribute column
9. Pivot name column using "Value" as value column. Under advanced options, select "Don't Aggregate"

When I click on one of the errors, the following message pops up:

"Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
List"

Workbook is attached.

[AliGW]

OK - without even looking at the file, I can now tell you what this probably is, as I use pivot - don't aggregate all the time! It will be that there are duplicate records. Make sure that you have selected all columns, right-clicked and removed duplicates before the pivot step. If this doesn't resolve it, let me know, and I'll take a look.

[LUKE92]

Ali - I feel like we're getting closer. That definitely cleaned up the query but didn't solve the problem. I've attached another workbook with 2 sheets containing either layout that I'm looking to create. It looks like I'll probably have to add an index column, but I'll have to tackle it later this weekend. Let me know if you can think of anything!

[AliGW]

Luke - without dummy data it's not really possible to help you to do this. Just empty grids don't tell us what we need to know.

Please create a before table and an after table with sample data showing what yo are trying to achieve.

[AliGW]

Attached is a sample file showing what you need to do to pivot the data. You should be able to work through the steps I've taken and apply them to your own query.

From this query you can also create the second data layout required - duplicate the query and amend the steps to suit.

Here's the M code:

Please Login or Register  to view this content.

[LUKE92]

Ali - I apologize if I didn't provide the correct amount of detail. I'm new to power query, internet forums, and file sharing.
That being said, the layout you have in your file is EXACTLY what I'm looking for! I'm wanting to use a separate workbook from the data for the query though.

I was able to recreate that layout in a separate workbook with the following code:

Please Login or Register  to view this content.

I used that code for each of the 5 P&L's, then combined them into one table with the following code:

Please Login or Register  to view this content.

You're suggestions have helped tremendously so far. I've attached a zipped folder so you can have all the data in one place.

The problem with applying this to the real world project that I'm working on is that I will to make separate queries for 216 worksheets. It's going to take quite some time if I use the method above. I'm hoping that I can import folders as data sources to consolidate some of these steps.

The attached zip folder contains the support folder with the 5 P&L's that I'm wanting to use as a data source for the query that I would like to be in the same layout as the "Power Query Test" file.

Is this possible with Power Query, or will I have to go down another route?

[AliGW]

See if this gets you any closer.

[AliGW]

Hi, Luke!

Just wondering if you were able to solve this with the suggestion I made above, or if you still need help with it?

[LUKE92]

Hi, Ali!

This is perfect for the dummy data I've provided. The actual data I'll be using is a bit more complex, but a similar layout. I'll try it out at work later this week and get back with the results. Thanks again for the help!

[AliGW]

No problem - just post here again if you need more help, but with data that is properly representative of the real data.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.