Pivot inside Powerpivot - sum all data to single row with unique ID

[Lennythelucker]

I like to pivot a datatable with a lot of values per row (for each product and day) to a single row per customer.
now I make a pivot table to excel to do this, link it to a table and bring it back to powerpivot.
then I can link it to other tables due to the unique ID
see image:

this side step to excel and back is not nice and I feel there must be a better way. I like to do this inside Powerpivot. is there an option I don't know about to do this sort of moddeling?

thanks for your help!

 

[MarcelBeug]

It can easily be done with Power Query (or "Get and Transform Data").

Tab data - get data - connect to your SQL Database and select your data - in Power Query select ID and customer column, choose Group By and define your aggregations.

Maybe you need to adjust some data types and now you can load your data into the Datamodel.

In the code below I use an Excel table instead of a SQL Database, but hopefully this will be enough for you to adjust to your specific situation:

let
    Source = Excel.CurrentWorkbook(){[Name="SQLData"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"ID", type text}, {"customer", type text}, {"period", Int64.Type}, {"sales", Int64.Type}, {"Product", type text}}),
    Grouped = Table.Group(Typed, {"ID", "customer"}, {{"Min period", each List.Min([period]), type number}, {"Max period", each List.Max([period]), type number}, {"total sales", each List.Sum([sales]), type number}})
in
    Grouped

Basically it means that your entire process is now done in 1 simple query.

 

[Lennythelucker]

great, thanks for your quick response. I am going to try this out.
I assumed power quiry would not work due to the high amout of data of some databases (5M+), but if I can pre group, it will work

And will this still be easy to refresh (one click) I like end users to be selfsufficient.

looks great.
I am going to dig in.

 

[Matt Allington]

Power query should be fine as long as you only use the UI (hence you get nice query folding). The other option is to just write some sql code and paste it in the power pivot query load wizard