Questionable application of PQ; transpose column headers into columns and append sets

[vtoaso]

Seasoned PowerPivot user but newbie to PowerQuery - I have a task in which I'd like to leverage PowerQuery for learning purposes but uncertain if the correct application. I have dozens of spreadsheets with the same format. Objective is to transform data into a tabular format versus its current pivoted format and create a data set for berry production.

I am new here and unable to post a picture - Ill do my best to describe -

There 2 essentially 2 column types; date and production, there are many production columns. Above each production columns there are 2 headers that im interested in creating columns for to associate with the relative production; acreage and unique ranch ID - these are static cells.

There are 2 PowerQuery functions I'm wondering are possible -

1. I'm familiar with the append feature with respect to other sheets, but within a sheet I essentially want to append the production and their corresponding dates atop one another - essentially how it would appear in a db.
2. Id like to create two additional columns for each production column - the corresponding Acres and RanchID, while maintaining column A (date). As static, non-repeating cells in the header, I cant figure how to do this in the PQ editor.

Since most of the information (time series and production) is pivoted the way I want, I dont think the pivot feature would be applicable.

Any guidance and insight is appreciated even if this can’t be done and I should go about it manually.

Thank you in advanced.

 

[Ken Puls]

Hi there,

You may not be able to post a picture yet, but you can upload a small sample workbook if you use the "Go Advanced" reply button. It might be easier if you could do that with a small (5 row sample) of the tables used as a data source, and what you'd expect the output to look like.

 

[vtoaso]

Hi Ken, thanks for the insight - did a lot of looking around but didn't catch that feature.

Posting image now...

BTW, I heard your interview on the PASS website with Jen Stirrup this weekend - I really enjoyed it and validates a lot of brainstorming Ive had around PowerPivot adoption in the enterprise. Its always nice to learn more about a "virtual" mentor on a professional level and assigning a personality/face to a name. Ill be picking up M is for (data) monkey as soon as I finish Rob and Avi's second addition of PowerPivotAndPowerBI.

 

[Ken Puls]

Awesome, glad you enjoyed the interview.

So, I added another column to your data table, as it would change the solution a bit. I think this is what you're after.

FYI, I didn't bother to keep the acreage, as I assume that is pretty static, and that you'd have that in a separate table linked via the RanchID.

For reference, the method used here is what we refer to as "Unpivoting Subcategorized Tables" in M is for Data Monkey. (Chapter 15, pages 123-127 explains exactly what I did here.)

 

[vtoaso]

Your table is Exactly what Im looking for. And correct, the acreage and technically even variety name is contained in the associated lookup or ranch dimension table associated via the RanchID.

Great, I'll be picking up the book earlier than expected. Have to ask ahead of time since I know you have a background in VBA - this this achieved in the ribbon or via M? Ive seen some M (Chris Webb) and I struggle a little bit as I have less of a programming background and much more of a data modeling background. DAX is intuitive to me, thus far M isnt as much so.

Thanks for the help.

 

[Ken Puls]

The solution is 100% ribbon driven, no manual M code edits needed, and no VBA required.

(And I'd agree that M is not the most intuitive language.)

 

[vtoaso]

Extending modification?

Hi Ken. Got the book and achieved what I needed to via the section that you cited. Thank you.

Issue now - I have many spreadsheets in this format, I'll need to repeat this process many times. I cut the M code out of the Advanced Editor and pasted it into a new solution. The only difference among these spreadsheet are the amount of varieties on a particular ranch, thus the columns measuring variety arent fixed. I extracted syntax from a spreadsheet that had 5 varieties, then tried to apply it (via pasting M) to a spreadsheet that had one variety - needless to say I get errors about the columns.

Before I spend too much time in troubleshooting this issue, do you have any recommendation on applying the same transformation to the same layout but with different "width"? Im thinking this comes down to the lesser of two evils; manually tweaking the M code or manually going through the process again.

 

[Ken Puls]

Ah, sorry. Edit that query and delete the second step (the one marked "Changed Type"). That has hard coded column names in it. After you do that I think you'll find it scales to different widths automatically.