Need advice on data model

[Anonymous]

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

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

In addition, here is also a good article about how to Pivot and Unpivot with Power BI for your reference. 

Regards

[Anonymous]

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

[Greg_Deckler]

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"