Power query : Column header with Volume & Year

[ClementA]

Hello Power BI community,

Beginner in Power BI and former user of Pivot table in excel, I am having a file with component yearly volume and vehicle yearly volume column (please see picture).  Power BI recognize volumes numbers but not the yearly date of component / vehicle.

I would like to create a chart with X axis years, Y axis Volumes of vehicle and series for the car nameplates.

I would also need to have component volume Y axis on a second chart.

Below you can see Series & X axis are revert as date is not recognize by Power BI :

In Excel I was usually switching date X& Y but here I imagine there is a smarter way to do it via Power query ?

Any pro tips to make this more smart and easy ?

Thanks a lot for reading and your precious help !

Clement

[dax · Accepted solution]

Hi @ClementA , 

You could refer to my sample for details

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIGYjMgNoJicygNkktUitWJhsuYArEJFJtCdYFUJYFVwURh5plBVVpCVSUrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"C Volumn 2012" = _t, #"C Volumn 2013" = _t, #"C Volumn 2014" = _t, #"C Volumn 2015" = _t, #"R Volumn 2012" = _t, #"R Volumn 2013" = _t, #"R Volumn 2014" = _t, #"R Volumn 2015" = _t, name = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "type"}, {"Attribute.3", "date"}})
in
    #"Renamed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ClementA]

Hi Zoe,

Awesome, looking at your example file, I managed to get the steps, unpivoting then fractioning

Thanks a lot works exactly how I was looking for...:) !

[amitchandak]

@ClementA , you might to unpivot twice and split column to get year

https://kohera.be/blog/power-bi/how-to-unpivot-twice/

https://radacad.com/pivot-and-unpivot-with-power-bi

[ClementA]

Hi @amitchandak,

Unpivot only the yearly volume column to lines ? and then split the title ?

Tried unpivot vehicle & component volume once where I hand up with this :

Should I then re unpivot it ?

Thank you,

Clément

[ClementA]

Hi  @amitchandak  !

Thanks a lot for your proposal, I was trying single unpivot, let met work on it and confirm you 🙂 !