02-17-2017 07:57 PM
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
01-31-2018 02:55 PM
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