powerbi - Split data into categories in the same row in Power BI

Question

I have a table that contains multiple columns with their named having either the suffix _EXPECTED or _ACTUAL. For example, I'm looking at my sold items from my SoldItems Table and I have the following columns: APPLES_EXPECTED, BANANAS_EXPECTED, KIWIS_EXPECTED, APPLES_ACTUAL, BANANAS_ACTUAL, KIWIS_ACTUAL (The Identifier of the table is the date, so we have results per date). I want to show that data in a table form, something like this (for a selected date in filters:

+------------+----------+--------+
| Sold items | Expected | Actual |
+------------+----------+--------+
| Apples     |       10 |     15 |
| Bananas    |        8 |      5 |
| Kiwis      |        2 |      1 |
+------------+----------+--------+

How can I manage something like this in Power BI ? I tried playing with the matrix/table visualization, however, I can't figure out a way to merge all the expected and actual columns together.

标签： powerbianalytics

Answer 1

It looks like the easiest option for you would be to mould the data a bit differently using Power query. You can UNPIVOT your data so that all the expected and actual values become rows instead of columns. For example take the following sample:

Date       Apples_Expected Apples_Actual
1/1/2019           1             2

Once you unpivot this it will become:

Date         Fruit        Count
1/1/2019 Apples_Expected    1
1/1/2019 Apples_Actual      2

Once you unpivot, it should be fairly straightforward to get the view you are looking for. The following link should walk you through the steps to unpivot:

https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

Hope this helps.