Forum Discussion
help for beginner power bi
If you need the data available in table visual, you could do a quick thing in Power Query. The easiest thing to explain would be the following code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyVNJRCijKTylNLnEEMkODXYCkoYGRAVQivSi1uFgpVidayYg05cZoyp3wKzfBotzTJQiXclOCyp3zcwtyUktSU8DqzbA4Hp96c2zq/XCrt0BT7wxkegVE4lRvSVB9QGpeSmZeOli1oQHxymMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, product = _t, curr = _t, #"txn ref" = _t, status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"product", type text}, {"curr", type text}, {"txn ref", Int64.Type}, {"status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"product", "curr", "status"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[status]), "status", "Count", List.Sum)
in
#"Pivoted Column"
Replace the source step with data you have and you should be good to go. Here is the end result:
Code simply is running a group by operation on Product, Currency and Status, calculating the number of rows. Then it Pivots the Status column, using Count as values.
- learner_232 years agoFrequent Visitor
But if I pivot and group, then I can't create bar chart (status vs count).
- PawelWrona2 years ago
Resolver II
So, you can leave your table as it is, and build the bar chart like it was presented already here. For the table visual put Product and Currency as details and 3 measures calculating count of items for dach Status.