cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gctaeger Occasional Visitor
Occasional Visitor

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

 

1 REPLY 1
stretcharm Senior Member
Senior Member

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

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