Forum Discussion
Solved
Line chart with special data
My data is an xlsx file and it looks like the following:
| Name | Day1 | Day2 | Day3 | Day4 | Day5 |
| A | 5 | 1 | 8 | 6 | 7 |
| B | 1 | 8 | 7 | 2 | 5 |
| C | 4 | 7 | 5 | 2 | 0 |
| D | 8 | 5 | 5 | 6 | 2 |
The value of A at Day1 is 5; the value of C at Day4 is 2 and so on.
My requirements are the following:
- Use 4 lines in one single line chart to show the trends for A, B, C and D.
- I can use a filter to show or hide any line.
The following image is what I need.
If I transpose my data like the following, it's easy to create the line chart but I couldn't filter out the line. For example, I cannot hide A and C just like the above image.
| Day | A | B | C | D |
| Day1 | 5 | 1 | 4 | 8 |
| Day2 | 1 | 8 | 7 | 5 |
| Day3 | 8 | 7 | 5 | 5 |
| Day4 | 6 | 2 | 2 | 6 |
| Day5 | 7 | 5 | 0 | 2 |
Thanks
Unpivot your data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIFYkMgtgBiMyA2V4rViVZyQhI1B2IjsEqQjDOQZQIVNYXKGIBlXKDqTaHYDCwbGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name " = _t, #" Day1 " = _t, #" Day2 " = _t, #" Day3 " = _t, #" Day4 " = _t, #" Day5 " = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name "}, "Day", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}}) in #"Changed Type"Then everything else works automatically.
1 Reply
Unpivot your data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIFYkMgtgBiMyA2V4rViVZyQhI1B2IjsEqQjDOQZQIVNYXKGIBlXKDqTaHYDCwbGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name " = _t, #" Day1 " = _t, #" Day2 " = _t, #" Day3 " = _t, #" Day4 " = _t, #" Day5 " = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name "}, "Day", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}}) in #"Changed Type"Then everything else works automatically.