Forum Discussion

ClementA's avatar
ClementA
Regular Visitor
6 years ago
Solved

Power query : Column header with Volume & Year

 

 

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

Need Help
Tips and Tricks
  • dax's avatar
    dax
    6 years ago

    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.

5 Replies

    • ClementA's avatar
      ClementA
      Regular Visitor
      6 years ago

      Hi  amitchandak  !

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

    • ClementA's avatar
      ClementA
      Regular Visitor
      6 years ago

      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

  • dax's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      ClementA
      Regular Visitor
      6 years ago

      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...:) !