Forum Discussion

Nozama's avatar
Nozama
Icon for Helper I rankHelper I
1 year ago
Solved

Pivot/Unpivot or Transpose Solution to plot line chart

I have the following structure in a table: Name Metric July 2025 August 2025 Sept 2025 Oct 2025 Nov 2025 Dec 2025 Jan 2026 Alpha Volume 0.85304 0.145932 0.558814 0.260746 0.927...
Need Help
  • DataInsights's avatar
    DataInsights
    1 year ago

    Nozama,

     

    Try this solution.

     

    1. In Power Query, select all Month Year columns and unpivot.

     

     

    Rename columns as desired.

     

     

    2. Create measure:

     

    Metric Value = SUM ( 'Table'[Value] )

     

    3. Create visuals:

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
1 year ago

Hi,

This M code transforms your data as follows

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Metric"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Metric]), "Metric", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Name", type text}, {"Attribute", type date}, {"Volume", type number}, {"Surface", type number}, {"Length", type number}})
in
    #"Changed Type"

Hope this helps.