I've completed both the PowerQuery and PowerPivot courses, and my head is still spinning a bit, but I do have a question on how best to handle some transactional data. This isn't my real example, but it exemplifies what I am trying to do. Let's say I have 2,000 rows of sales transactions. For each transaction, I know the geographic territory the transaction was made in e.g. East, West, North and South. In actuality, my data is about about 3,000 rows long and I need to summarize it into categories that can have 100 distinct values, not just the four that I'm referring to in my example. One could think of a very granular product category that can have a lot of values.
I'm not really interested in the raw data itself, but just the pivot of the data to get something like this, by counting the transactions in each "direction" as a percentage of the total transactions:
Direction Pct
East 10%
West 30%
North 40%
South 60%
In my actual dataset, there would be 100 of these rows. I can achieve the above by creating a separate pivot table, but then I don't know how to import a pivot table into my data model. I don't think one can do that though. I think I need to create a measure that returns, not a value, but a table of values. But, I'm not sure if that is possible either. You might ask what I will end up doing with the above table. I'll create a separate measure (from another table) as the sum of all my transaction costs. Let's say the total transaction cost is $100.
Then I'll want to use a calculate in another table as follows:
East region transaction cost = $100 * 10%, which is the measure multiplied by the pivot value for each region. In this way, I can assign my total transaction cost to the region in which it occurs. This is just a small part of the model, but an important one.
Is there a best practice for doing the above in the PowerQuery/PowerPivot arena?
Sincerely,
Scotty81
Hi Cory,
If you don't need the actual transactions in your model then you can use Power Query to group the data into the 100 'Direction' categories and then load it into Power Pivot (this also helps keep your model small and free of clutter). From there you can create your measures to calculate the % allocation of the costs.
I hope that points you in the right direction.
Mynda
Hi Mynda,
Unfortunately, I might still need pointing in the right direction. Also, I don't know if I did a good enough job at highlighting all the machinations of what I need to do with my raw data to get it into its final form. I attached a seemingly very simple example of costs for 3 departments, which roll up to 2 profit centers. In that example, I illustrate what I do with separate data sources. I annotated the example, so I hope it is easy to follow.
I'll also have to review Power Query's Group function since it sounds like that function can perform some aggregation like a Pivot Table would. I'll also need to think about what has to/should stay in Power Query vs. what has to/should be in Power Pivot. If you have any further feedback after looking at my example, I would certainly appreciate it.
Sincerely,
Cory
Hi Cory,
Thanks for the mock up file. It helped a lot. You can do most of this in Power Query. Showing the balancing credit for Telephone and Computer Equipment on their respective 'Department' lines isn't easy and I haven't done it in the attached file as I wondered if you really needed to show it, or it was done for the purpose of showing your workings for my benefit.
Anyhow, take a look at the attached file. You'll see the following queries:
1. Telephone % usage converts the 'Telephone' table values into percentages you can use to allocate the Telephone costs.
2. Raw Cost Data is simply the RawData table
3. Telephone Costs to Allocate is the total telephony cost amount in a list.
4. Computer Equip Costs to Allocate is the total Computer Equip cost amount in a list.
5. Allocated Telephone Costs is the total Telephone Cost to Allocate x the Telephone % usage
6. Total Costs is the Raw cost & Telephone costs by Department.
I stopped there because I didn't have the Computer Equip % apportionment info and I thought this might be enough to send you in the right direction to complete it. That said, I don't know how many cost departments you need to allocate and if there are a lot then it might be better done in Power Pivot as you could also use DAX to do the allocation of costs.
Mynda
Hi Mynda,
Yes, thank you for your response. The worked out example was enough for me to further the solution of this example. More importantly, it provided me with some much needed syntax with which to do basic Power Query operations. Specifically, I saw how you divided numbers in a column by the sum of those numbers in that same column. When I looked at the M code behind that step, it looked pretty daunting, and I didn't know if I would have to write that by hand. But, I see that if you know the more basic syntax of the sum operation (via List.Sum), that's all you need for adding a custom column in that step.
And, I now see how to bring in the results of another query via the #"QueryName" command. I also see how you saved the results of some queries as a List to be used in subsequent math operations That's not intuitive, so I'll have to read up on what the "Convert to List" command gives you - perhaps a numeric value, which is what you want.
From seeing the power of Power Query, no pun intended, and in considering my analysis needs, I think I'll keep the remainder of the processing in Power Query since I do need to continue to drill down to the raw data in the final pivot data I produce. I can't say I have the example completely worked out, but your response will certainly help me significantly get down the road.
Thanks again!
Cory
Great to hear you've got a good enough grounding now to continue yourself.
To help with your understanding of some concepts; I cover the grouping in session 4.16 of the course and Lists and Records in 6.07. Although, in your file the lists worked back to front i.e. I right-clicked the Telephone cost > Drill Down. Then on the Ribbon in the contextual 'List Tools: Transform' tab you can 'Convert to.. > List'.
Everything I did in the example file was done with the GUI. I didn't have to dive into the Advanced Editor at all, so that should make it easier to get your head around, but I'm here if you have further questions.
Mynda
