Forum Discussion
creating a table with pivoting multiple columns.
Hi,
Currently, I have the below table and I'm trying to create a new table where I have:
- current_arrival_fiscal_year_week in columns across the top
- SUM(order_qty) for each current_arrival_fiscal_year_week
- SUM(po_sku_cost) for each current_arrival_fiscal_year_week
- There would be a row for each sku and store_no.
- Instead of a row for each sku, store_no and current_arrival_fiscal_year_week.
The table would look like this, but be a table in the data model.
I've tried using the pivot column functionality in power query, but it only allows for pivoting one column instead of having both the po_sku_cost and order_qty for each week. Additionally, I've tried a few other ways as well.
After this, I'd be able to join to another table that is aggregated at the sku, store_no level.
Advice would be much appreicated, thanks so much.
2 Replies
Anonymous not sure why would you like to pivot it. AS a best practice and data modeling practice, the unpivoted table is the way to go. What is the functionality you are not able to achieve the way your current data is?
The functionality I'm trying to achieve is creating a table that looks like the below.
- The PO data that I've spoken to in the OP on the right side of the below visual is aggregated at date, sku and location.
- The inventory data highlighted in yellow is aggregated at just the sku and location level.
- By pivoting the PO data, so I have the weeks in columns, I could then join the Inventory and pivoted PO data on the sku and location columns .
