Occasional Visitor
Posts: 1
Registered: ‎02-17-2017

Help with Edit Query Power BI

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


Established Member
Posts: 246
Registered: ‎07-05-2017

Re: Help with Edit Query Power BI


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


I detailed a similar solution to in this post