Forum Discussion
Need advice on data model
Hello!
as a Power BI beginner I would appreciate some advice for a (probably rather simple) data model.
My data source is one single table (>200.000 rows).
It lists order forms (unique ID is the Order Nr). Each Order has been examined in more than 50 categories. Each categories field is Boolean. So the table looks somewhat like that:
OrderNr | Cat.1|Cat.2|Cat.3|....|Cat.50 ----------------------------------------- 1 |true |false|false|....|true 2 |false |false|true |....|false
99999 |true |true |true |....|true
....
I would like to show a bar chart that lists (selected) categories on one axis and the number of orders having a "true"-value in this category on the other axis. Also this bar chart should act as a filter: when clicking on category-name a table-visualization should only display the order numbers where this category has the value "true".
Thanks for any advice!
chris
Hi Anonymous,
In addition, here is also a good article about how to Pivot and Unpivot with Power BI for your reference. :smileyhappy:
Regards
3 Replies
Take a look at this. Basically select your categories columns and unpivot:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSopKk0FUmmJOcWp6NxYnWglIyRJdBqsGKTIGKETCwVSYYLLDpiaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OrderNr = _t, Cat.1 = _t, Cat.2 = _t, Cat.3 = _t, Cat.4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderNr", Int64.Type}, {"Cat.1", type logical}, {"Cat.2", type logical}, {"Cat.3", type logical}, {"Cat.4", type logical}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"OrderNr"}, "Attribute", "Value") in #"Unpivoted Columns"Hi Anonymous,
In addition, here is also a good article about how to Pivot and Unpivot with Power BI for your reference. :smileyhappy:
Regards
Thank you v-ljerr-msft and Greg_Deckler !!
It seems that "unpivot" is what I was looking for: Dashboard is now working as intended!
Cheers,
chris
