Complex (in my opinion!) data transpose and filter

[PurpleTrousers]

Hello - I am a beginner in PowerBi, I have watched a number of videos and searched for the solution to my issue with no luck. I am trying to manipulate my input data which is of a similar format to the dummy data below (but with many more columns):

I would like to be able to filter on the date, option and category and return the associated volume and price in a format to plot a line graph with the output, which I assume should look something like this:

(I would also like to sort by vol)

I have spent some time trying to figure out how to do this without any luck so was hoping someone will take pity and point me in the right direction 🤔

Thanks in advance 

[PurpleTrousers]

Perfect thank you

[rohit_singh · Accepted solution]

Hi @PurpleTrousers ,

Please try the steps given below in Power Query :

1) Select all columns except Date, Option and Category, and then click on "Transform" --> "Unpivot columns"

You will see that the column headers for price and volume are now in rows.

2) Split the attribute column from non-digit to digit. This will split the column into a text column and a number column. We only need the text column named "Attribute.1" since that has price and volume headers

3) Finally, select the "Attribute.1" column and pivot it over the "Value" column using the sum aggregation. 

Here is the final result :

You can now load this table to the report view and filter by date, option and category.

Here is the sample M-code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMjJR0lQyB2hNJGRNOxOliMcaLcGCPquMaIOq4xpo5rjMlzTSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Option = _t, Category = _t, Price1 = _t, Volume1 = _t, Price2 = _t, Volume2 = _t, Price3 = _t, Volume3 = _t, Price4 = _t, Volume4 = _t, Price5 = _t, Volume5 = _t, Price6 = _t, Volume6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Option", Int64.Type}, {"Category", type text}, {"Price1", Int64.Type}, {"Volume1", Int64.Type}, {"Price2", Int64.Type}, {"Volume2", Int64.Type}, {"Price3", Int64.Type}, {"Volume3", Int64.Type}, {"Price4", Int64.Type}, {"Volume4", Int64.Type}, {"Price5", Int64.Type}, {"Volume5", Int64.Type}, {"Price6", Int64.Type}, {"Volume6", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Option", "Category"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.1]), "Attribute.1", "Value", List.Sum)
in
    #"Pivoted Column"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[rohit_singh]

Hi @PurpleTrousers ,

If this solution worked for you, please mark this answer as the solution to help others as well. 
Appreciate your kudos!

Kind regards,

Rohit