Split row into different categories from colum name

[Anonymous]

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 example, if the selected date is '2019-12-23'):

Sold Items	Expected	Actual
Apples	10	15
Kiwis	2	1
Bananas	8	5

Here is what the datasource table looks like:

Date	APPLES_EXPECTED	KIWIS_EXPECTED	BANANAS_EXPECTED	APPLES_ACTUAL	KIWIS_ACTUAL	BANANAS_ACTUAL
2019-12-23	10	2	8	15	1	5
2019-12-22	5	14	5	4	8	2

How can I manage something like this in Power BI Desktop ? 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.

[mwegener]

Hi @Anonymous ,

use Power Query.

1. Unpivot Columns

2. Split Column by Delimiter

3. Pivot Columns

You may download my PBIX file from here.
Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Will this work if I have more columns other than the ones mentionned above ?

[parry2k · Accepted solution]

@Anonymous you can use following script to transform the data , go to query editor, add blank query and paste following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1NNI1MlbSUTI0ABJGQGwB4piCCCA2VYrVQVJoBBYCSplAGSZQDUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, APPLES_EXPECTED = _t, KIWIS_EXPECTED = _t, BANANAS_EXPECTED = _t, APPLES_ACTUAL = _t, KIWIS_ACTUAL = _t, BANANAS_ACTUAL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"APPLES_EXPECTED", Int64.Type}, {"KIWIS_EXPECTED", Int64.Type}, {"BANANAS_EXPECTED", Int64.Type}, {"APPLES_ACTUAL", Int64.Type}, {"KIWIS_ACTUAL", Int64.Type}, {"BANANAS_ACTUAL", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Fruit", each Text.BeforeDelimiter([Attribute], "_"), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Type", each Text.AfterDelimiter([Attribute], "_"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Attribute"})
in
    #"Removed Columns"

[Anonymous]

I do not want to add a new table however, I want to keep it in my own table to keep the filters set by the rest of the visuals

[parry2k]

@Anonymous you are not adding new table, but you are transforming your existing table for better design, and get you what you are looking for. It is all about best practices, don't get caught into just getting the work done but think about how it will scale.

[parry2k]

@Anonymous yes solution will work with new products, as far as the format of your data stays the same, it doesn't matter if there are new products, more rows etc.

[parry2k]

@Anonymous you will get following output