Forum Discussion
Split row into different categories from colum name
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.
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"
7 Replies
- parry2k
Super User
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"- parry2k
Anonymous you will get following output
- 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.
- mwegener
Most Valuable Professional
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.
