Forum Discussion
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.927412 | 0.938576 | 0.941928 |
| Alpha | Surface | 0.639594 | 0.845205 | 0.506164 | 0.789447 | 0.512091 | 0.180707 | 0.294231 |
Alpha | Length | 0.364118 | 0.654881 | 0.232321 | 0.164225 | 0.434164 | 0.532053 | 0.047781 |
Gamma | Length | 0.463719 | 0.525074 | 0.719842 | 0.531754 | 0.850802 | 0.85733 | 0.457149 |
Gamma | Surface | 0.102247 | 0.543021 | 0.828911 | 0.148918 | 0.451905 | 0.778538 | 0.565927 |
| Gamma | Volume | 0.755331 | 0.397014 | 0.371991 | 0.975135 | 0.905638 | 0.957897 | 0.256631 |
| Epsilon | Length | 0.797444 | 0.079887 | 0.16339 | 0.417141 | 0.85473 | 0.583854 | 0.374728 |
| Epsilon | Surface | 0.259266 | 0.021544 | 0.367167 | 0.161125 | 0.102362 | 0.675798 | 0.317543 |
| Epsilon | Volume | 0.563734 | 0.348062 | 0.722632 | 0.764814 | 0.526606 | 0.261507 | 0.138122 |
| Beta | Volume | 0.098589 | 0.412391 | 0.637296 | 0.792191 | 0.260899 | 0.510029 | 0.925143 |
| Beta | Surface | 0.207163 | 0.14652 | 0.438347 | 0.716766 | 0.948066 | 0.049009 | 0.401906 |
| Beta | Length | 0.837054 | 0.93943 | 0.057844 | 0.242815 | 0.473492 | 0.536631 | 0.447548 |
I want to be able to plot line charts for series "Volume", "Surface", "Length" over the time axis (X) as column headers. The slicer is the Name column.
For the life of me, I have tried pivoting, unpivoting, transposing and what not, and can't figure out how to get the table into a proper format for plotting this. Please help. Thanks!
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:
2 Replies
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:
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.
