creating a table with pivoting multiple columns.

[Anonymous]

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. 

[parry2k]

@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?

[Anonymous]

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 .