Power BI Convert Columns to Rows

Question

So I have an Excel file where the first column is a Donor and then there are pairs of columns for 10 years of Amount and Date as shown below:

Donor Name    2019 Amt  2019 Date 2018 Amt  2018 Date 2017 Amt  2017 Date
------------- -------- ---------- -------- ---------- -------- ----------
Someone Here        50 08/21/2019
Someone Else       150 06/15/2019       75 05/04/2018
Another One                                                125 03/03/2017

And what I am looking for is:

Donor Name   Amount       Date
------------ ------ ----------
Someone Here     50 08/21/2019
Someone Else    150 06/15/2019
Someone Else     75 05/04/2018
Another One     125 03/03/2017

When I just unpivot the columns other than Donor name I get pairs of records for each entry. It's closer to what I am looking for but not sure how to then transform that to the above. Perhaps pivoting is not the answer?

Answer 1

You would need to unpivot those columns and then pivot them back.

1) Unpivot Donor Name

= Table.UnpivotOtherColumns(#"Changed Type", {"Donor Name   "}, "Attribute", "Value")

2) Split Attribute column by space delimiter or by first 4 characters (we'd need the year value later). Make sure there are no leading or trailing spaces in header names.

= Table.SplitColumn(#"Unpivoted Other Columns", "Attribute",
                    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
                    {"Attribute.1", "Attribute.2", "Attribute.3"})

3) Remove Attribute.3 column, leave Attribute.1 column though, in case the same donor has data that spans several years (Someone Else in this example)

= Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.3"})

4) Pivot back. Pivot Attribute.2 based on Value

= Table.Pivot(#"Removed Columns", 
              List.Distinct(#"Removed Columns"[Attribute.2]), "Attribute.2", "Value")

5) (Optional) Attribute.1 column, the column that contains year information, now can be removed if needed

= Table.RemoveColumns(#"Pivoted Column",{"Attribute.1"})