CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
Chris
Chris

Reputation: 683

Power BI -- how to create the desired table for use in a bar chart?

I'm pretty new at Power BI (so forgive my rough terminology), and I'm trying to create a bar chart from some existing financial data. Specifically, I'd like to know how to transform my data. I've looked at DAX and python, and can't quite figure out the right commands.

My existing table looks like the following. The set of categories are arbitrary (not known up front, so can't be hardcoded), same with the set of years.

Category  2002  2003  2004  2005
A         $10   $75   $75   $75 
B         $75   $59   $75   $79 
C         $15   $32   $13   $5 
B         $23   $12   $75   $7 
C         $17   $88   $75   $15

And I want my output table to have the number of rows as the number of unique categories, totaling up the dollar amounts for each year.

Category  2002  2003  2004  2005
A         $10   $75   $75   $75 
B         $98   $71   $150  $86
C         $32   $120  $88   $20

What's the best way to roll up the data this way? I intend to use the resulting table to make a composite bar chart, one bar per year.

Thank you!

Upvotes: 0

Views: 283

Answers (2)

Peter
Peter

Reputation: 12325

  1. Avoid Excel-style cross-tables in Power BI. In the PowerQuery Editor transform your table by selecting Categorie and then Unpivot other columns

enter image description here

  1. Back in the designer view you can directly use this data to create a bar chart:

enter image description here

  1. If you like you can also create an aggregated table from your data with the calculated table expression
Aggregated = 
SUMMARIZE(
    'Table',
    'Table'[Category],
    'Table'[Year],
    "Sum", SUM('Table'[Value])
)

but that's not needed for your purpose.

enter image description here

Upvotes: 3

Ozan Sen
Ozan Sen

Reputation: 2615

Here is the full M-Code to achieve your goal: Just change the source step with your source file:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABLmpkhErE60khOMb2oJl7EEyziD9ID4xkYgljFIDVyLEYhraATXgtBhDiQsLGASQANiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"2002" = _t, #"2003" = _t, #"2004" = _t, #"2005" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"2002", Int64.Type}, {"2003", Int64.Type}, {"2004", Int64.Type}, {"2005", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Category", "Year"}, {{"Total", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Year]), "Year", "Total", List.Sum)
in
    #"Pivoted Column"

If we test it: GG_Editor

Upvotes: 1

Related Questions