3 ms·
I am regarded as a decent Excel user, and in my field, Finance, that is saying at least something. However, I had never tried power pivot until today. I was a
by jimnotgym 3y ago
I am regarded as a decent Excel user, and in my field, Finance, that is saying at least something. However, I had never tried power pivot until today. I was a bit annoyed that it can't handle many to many relationships. Time to dust off my SQL skills and do it the old fashioned way...
I had a table of sales transactions, and a table of stock balance. I wanted to join them on the item sold so per item I had stock balance and a sales value per sku. I was suprised it wouldn't do it. It returned in less than a second as a sell query.
- proamdev123 3y agoThe workaround for many to many relationships isn’t too hard. What you have to do is create another table containing unique values of items sold, and then make 1:many relationships from that table to the other two. You can easily make the unique value table by copying and pasting all of the items sold into a single column on a new sheet, highlight them all, and then Data -> Drop Duplicates. It’s a little annoying, but not hard.
- jimnotgym 3y agoBut I have line level data that needs summing in the sales table. The workaround is one table of stock, one of sales and a sumif on the sales to get the stock. But now I don't have a pivot table
- proamdev123 3y agoThat’s what Power Pivot is for. Once you create the additional table and relationships as I described, you should be able to create a pivot table using data from multiple tables. No SUMIFs required. Unless I’m misunderstanding something.