How to create new Power BI table where each column (except date) is converted into new rows with duplicated date

Question

I want to extract data from a table in power BI such that all column titles form a new row for each date. For example, say I have the following table:

Date	Apples	Bananas	Cucumbers
31 Jan 2024	50	40	20
29 Feb 2024	40	30	20

And I want to make a new table like this:

Date	Things	Prices
31 Jan 2024	Apples	50
31 Jan 2024	Bananas	40
31 Jan 2024	Cucumbers	20
29 Feb 2024	Apples	40
29 Feb 2024	Bananas	30
29 Feb 2024	Cucumbers	20

Is there a way to do this?

I tried using Dax code but haven't gotten any progress so far.

Answer 1

For each fruit/vegetable column I create a temporary table with columns for the date, the name of the fruit/vegetable as "Things", and its corresponding value in the "Prices" column. Then, I use UNION to combine these temporary tables into a single table and unpivot your original table.

  UnpivotedTable = 
    UNION(
        SELECTCOLUMNS(
            MyTable,
            "Date", MyTable[Date],
            "Things", "Apples",
            "Prices", MyTable[Apples]
        ),
        SELECTCOLUMNS(
            MyTable,
            "Date", MyTable[Date],
            "Things", "Bananas",
            "Prices", MyTable[Bananas]
        ),
        SELECTCOLUMNS(
            MyTable,
            "Date", MyTable[Date],
            "Things", "Cucumbers",
            "Prices", MyTable[Cucumbers]
        )
    )