Forum Discussion
Line chart in Power BI with excel tables in historical form
I'm trying to visualize geotechnical instrument information in Power BI in a line chart.
The original database contains each instrument as a column and the rows correspond to the dates on which a reading was recorded, in each cell the values recorded by the instrument on the respective date are recorded.
How can I design it to give me a historical chart with all the instruments in the entire date range?
Thanks for the reply from Selva-Salimi.
Hi Anonymous ,
On the basis of Selva-Salimi's answer, I will add a few more details:
1.In Power Query Editor, select Date column and click "Unpivot Other Columns".
Result:
2.Create a line chart with Date as the x-axis, Value as the y-axis, and Attribute as the legend:
3. If you need to do further analysis, it is recommended that you create a calendar table and create a relationship with the table. For example:
DateTable = ADDCOLUMNS( CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "Month", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "Weekday", WEEKDAY([Date]), "WeekdayName", FORMAT([Date], "dddd") )Relationship:
Best Regards,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Selva-Salimi
Solution Sage
hi Anonymous
after uploading your data, you can go transform data>> transform>> unpivot , then the structure of your table would cover your expectations.
If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
Thanks for the reply from Selva-Salimi.
Hi Anonymous ,
On the basis of Selva-Salimi's answer, I will add a few more details:
1.In Power Query Editor, select Date column and click "Unpivot Other Columns".
Result:
2.Create a line chart with Date as the x-axis, Value as the y-axis, and Attribute as the legend:
3. If you need to do further analysis, it is recommended that you create a calendar table and create a relationship with the table. For example:
DateTable = ADDCOLUMNS( CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "Month", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "Weekday", WEEKDAY([Date]), "WeekdayName", FORMAT([Date], "dddd") )Relationship:
Best Regards,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
