Line chart in Power BI with excel tables in historical form

[gab_7]

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?

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from Selva-Salimi.

Hi @gab_7 ,

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,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[gab_7]

Thanks, I undestand a part of it. But how can I do it if I have an array like this in excel? How should i modified it?

[v-linhuizh-msft]

Hi @gab_7  ,  

There doesn't seem to be any difference between this data you have in excel and the one I used for my test, just perform the same operation.

If I have misunderstood your question, please clarify in your next reply.

Best Regards,
Zhu

[Selva-Salimi]

hi @gab_7 

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.