pivot 2 out of 3 columns in either PowerQuery or PowerPivot

[alex1alex]

I have this:
SITE | GROUP | COUNT
NY | Finance | 5
NY | IT | 2
LA | Finance | 1
LA | Actors | 100

and want this:
SITE | Finance | IT | Actors
NY | 5 | 2 |
LA | 1 | | 100


I'm sort of doing a group by and pivot not really....

I'd like to do this in PowerQuery...but am open to options (preferably PowerPivot)

Any ideas?

Thanks,
Alex

 

[alex1alex]

Oh, so, to clarify. Yes, you can take the above, once it's in the Data Model< and just do a regular pivot to get the "and want this" table but I have a next step where I want to relate the table to another table in the data model....hence the question

 

[scottsen]

Can I refuse to answer, because I think your original format is superior?

 

[alex1alex]

ummmm....I'm not getting the joke

so, i want it in the 2nd format because I need that 1st column without duplicates so that I can create a relationship with a similar column in another data table.....

 

[scottsen]

It wasn't totally a joke, in that I *do* prefer the first format. If possible, i would pull the unique site values into a separate table, and use that for the relationships.

With the first format, you can easily write Total := SUM(Table[Count]) and be done.

In the 2nd format... you are a sad panda.

 

[alex1alex]

Hmmm, so, I don't follow as I need 1)the column with unique SITE values and 2) to be able to individually reference the GROUP values for for the SITES.

I did figure it out after going back and and looking in Chris Webb's Power Query Book. It can only be done with the M language and not the GUI interface.

It's a one liner:
TableIWant = Table.Pivot(TableIHave, {"Finance","IT","Actors"}, "GROUP", "COUNT", List.Sum)

The bummer here is that the 3 new column headers need to be manually grabbed from the TableIWant data and hand entered. (well, I imagine there's a way to do it programmatically but i wasn't going to spend another 3 hours doing it. )

Happy to try and understand your method though Scott.

 

[scottsen]

Granted I only vaguely know what you are doing, but this is how I would (by default) model this:

This assumes that your "count" column has meaning "in aggregate", such that SUM(Data[Count]) returns a meaning value, that can be slices by site and group.

Other than just being "easier", it is also better for performance to have less columns.

 

[alex1alex]

Thanks, that's helpful.

I sort of go back and forth on the direction of the relationship. Interesting to see how others do it.
(the way you show, if you can use the data in the values area of the pivote, but you need the opposite direction if you want to use the =related to pull the data back to then display it in the ROWS field of the pivot.
Haven't really figured out a way to have it both ways without duplicating queries.

 

[scottsen]

I'm not totally getting your point/question. So, I will just ramble

Actually, go look at What Is Power Pivot? | Tiny Lizard for a discussion point. That is a really typical model, there is 1 fact table, and a bunch of "lookup tables" for the dimensions. The data/fact tables just have Id's that point back to Customer/Geography/etc.

If you need to "filter" your data... you want those fields to come from the Lookup tables. Products[EnglishProductName], Territories[Country], etc. And that is the "direction" of the filtering... from the lookup table, it flows into the data (Sales, fact) and hides rows there.

Now, if you have *two* fact tables... nothing really changes, except that the lookup tables is related to both fact tables. You still want the lookup table to do the filtering... and it will filter BOTH tables.

 

[Useful]

Hello!

if your question still is actual then try this

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#" GROUP "]), " GROUP ", " COUNT", List.Sum)
in
#"Pivoted Column"