Help with Edit Query Power BI

[gctaeger]

New to power bi and looking at best steps to resolve imported excel document. Spreadsheet has column A as prod name and columns B to M are the months of the year which show the product volumes for each prod name. Column N to Y are the prices for each product name. Row1 shows the months/year..see below

        A                   B     C       D......  .M        N     O       P......    Y   

1      ProdName  J17  F17    M17   D17     J17  F17    M17   D17

2      Silver            5    5        10      12       $12   $12   $15    $18

3     Gold              8    6         5        10      $18   $20   $22    $24

Need to edit query of imported data to show

Prod Name     Date     Volume     Price

Silver               J17        5               $12

Silver               F17       5               $12

.

.

.

Gold               D17       10             $24

Thanks in Advance

[stretcharm]

Use Unpivot to split the columns into rows.  Promote 1st row to headers

If I'm understanding it correctly you may need to split the volumes and prices, unpivot them then merge together.

Create a copy of orignal source query delete the columns for volumns. In the original query delete the prices.

Give the Columns MonthNo Names as they get turned to attributes.  You can add a computed col to turn the month attribute to a date.

Select ProductName an unpivot other columns on each query

create a composite key from product and date copies of the columns then combine with a delimiter char

Use the key to merge query 2 back to the original and expand to get the price on the row.

Here is a couple of posts on unpivot

http://radacad.com/pivot-and-unpivot-with-power-bi

https://businessintelligist.com/2015/05/19/power-bi-tutorial-unpivot-feature/

I detailed a similar solution to in this post

http://community.powerbi.com/t5/Desktop/Actual-versus-projected/m-p/348465#M156474