CODEWITHSUNDEEP
powerbipowerquery
Yvonne
Yvonne

Reputation: 65

Visualization in Power BI

The left part is the data, and the right part is my desire output in the Power BI

I am new to Power BI Desktop and Power BI Query. The left part is the data, and the right part is my desired output in Power BI. I tried to use the Matrix Report Visual to format my data like the table on the right, but it only allowed me to select the Product column in the row. I hope to get the desired output without creating separate tables for each quarter.

Upvotes: 0

Views: 219

Answers (1)

horseyride
horseyride

Reputation: 21413

You could bring your data into powerquery, and use this code to transform the data. You'd have to modify it to show your actual table name (Table1) and the name of the columns as appropriate. It basically groups on index/product to find the most recent target sale, expands the data back, then pivots to get the sideways view

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"index", "product"}, {
    {"data", each   _, type table },
    {"most_recent_target_sale", each  Table.Sort(_,{{"time", Order.Descending}}){0}[target_sale], type number}
    }),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"time", "sale"}, {"time", "sale"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data",{{"index", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[time]), "time", "sale", List.Sum)
in  #"Pivoted Column"

enter image description here

Upvotes: 2

Related Questions