Create multiple rows from a single row in PowerBI

0
votes

I have a sales dataset where, in addition to (Sale#) and several foreign keys ID1, ID2, ID3, ID4 I have up to 3 Invoices (associated with a Sale#). E.g. Invoice#1, SaleAmount1, Date1, Invoice#2, SaleAmount2, Date2, Invoice#3, SaleAmount3, Date3; all as columns.

I need these three invoice information as rows (as shown below ) instead of columns. Any idea how can it be done in Power BI?

Sales#  ID1  ID2 ID3    Invoice#     SaleAmount      Date
----------------------------------------------------------
Sales#1 123 XYZ  A234Y  Invoice#1    SaleAmount1     Date1
Sales#1 123 XYZ  A234Y  Invoice#2    SaleAmount2     Date2
Sales#1 123 XYZ  A234Y  Invoice#3    SaleAmount3     Date3
1
powerbipowerqueryunpivotpowerbi-desktop

1 Answers

2
votes

Yep, you just need to unpivot sets of columns.

For example, if you select the 3 date columns in the following table

Sales#  ID1  Date1     Date2     Date3
-----------------------------------------
Sale#1  123  1/1/2018  1/2/2018  1/3/2018
Sale#2  456  2/2/2018  3/3/2018  4/4/2018

and go to Transform > Unpivot Column in the query editor, then you'll get this:

Sales#  ID1 Attribute   Value
--------------------------------
Sale#1  123 Date1       1/1/2018
Sale#1  123 Date2       1/2/2018
Sale#1  123 Date3       1/3/2018
Sale#2  456 Date1       2/2/2018
Sale#2  456 Date2       3/3/2018
Sale#2  456 Date3       4/4/2018

Then pick which column(s) you want to keep and rename appropriately.

You can do the other sets of 3 columns in the same way.