Multi line plot from multiple tables with no relation

[barathrk]

Hi,

I am a beginner user of Power BI. I want some help plotting a multi-line graph from multiple tables.

For example, I have the following table.

Voltage	Current	Time	Temperature 1	Temperature 2	Temperature 3	Capacity
4	10	1	25	24.5	26	1.1
4.001	9.99	2	25.5	24	26.8	1.5

(Upto 7000 rows of data)

Similar to the above table, I have 10 tables with all the 7 columns. It is to be noted that the values will not be the same. However, the range of values will be the same. For instance, the range of capacity in all 10 tables will be 0 to 4.8. The case is similar with all the other columns too. At the same time, I do not know what is the maximum value for a particular column too. For instance, in the temperature 1 column, the starting value will be 24 but the maximum value will not be known in prior (which is possible to find through maximum function/formula)

Now, I want to do a multi-line plot (10 lines) plotted against a particular x and particular y from all the tables. 

For example, I want to plot capacity on the x-axis and voltage on the y-axis in a multi-line plot from all 10 tables.

I cannot fix a particular column as x or a particular column as y. For instance, in the above example, I mentioned capacity on the x-axis, whereas, in another line plot, I may need capacity on the y-axis and current on the x-axis.

I prefer to avoid any coding either in this platform or any other tools. I already use Origin or Matlab to do the plots. However, I turned to Power BI as I need a quick view of graphs in multiple scenarios like excluding the data from 2 tables in a plot, knowing the maximum of a particular data point in a plot, or knowing the value of a column for a particular data point in a plot, etc, which I think I can achieve through this.

Kindly help in case you can understand my requirements, as I have watched several videos and read several blogs all of which demonstrate with date/ year, which is working, whereas in my case it is not. 

[grazitti_sapna · Accepted solution]

Hi @barathrk 
In Power BI Desktop, you can plot a multi-line graph from multiple tables dynamically by following these steps:

Step 1: Combine Your Data into a Single Table

Since you have 10 tables with the same structure, the best approach is to combine them into a single table to make visualization easier.

Method 1: Using Power Query (Recommended)

1. Go to Power Query Editor

   • Click on Transform Data in Power BI Desktop.

2. Append Queries

   • Load all 10 tables into Power Query.
   • Select any one of the tables and go to Home > Append Queries > Append Queries as New.
   • Add all 10 tables into the append operation.
   • Click OK → This will create a single table with all your data.

3. Add a "Source Table" Column (Optional but Recommended)

   • If you need to distinguish which row comes from which table, add a custom column that stores the table name.

4. Close & Apply

   • Click Close & Apply to load the combined data into Power BI.

Step 2: Create a Multi-Line Graph

Now that all data is in a single table:

1. Go to Report View and insert a Line Chart.
2. Drag and Drop Fields
   • X-Axis: Choose the desired column (e.g., Capacity or Current).
   • Y-Axis: Choose the desired column (e.g., Voltage).
   • Legend: Use the “Source Table” column (if added) to differentiate the lines.
3. Enable Dynamic Selection of X and Y Axes
   • Create a Disconnected Table with all column names (Capacity, Voltage, Current, etc.).
   • Use Switch Measures to allow users to select X and Y dynamically via slicers.

Step 3: Add Slicers for Interactivity

• Add a Slicer to let users filter out specific tables.
• Add Slicers for dynamic X and Y selection.

Notes:

• No coding required.
• Dynamic selection of X and Y axes.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[v-denglli-msft · Accepted solution]

Thanks for the reply from grazitti_sapna and MFelix, please allow me to add some more information.

Hi @barathrk ,

I tested grazitti_sapna's solution and it might meet your needs.
Below is the testing process.

In the Power Query editor, add a custom column for each table to identify the source table of the data.
This way, after merging all tables, you can easily filter the data of specific tables by this custom column.

Create a field parameter table using the fields from the merged table and create a slicer.
Later the field parameters can be used as the x-axis to enable dynamic selection of the x-axis.

Create a disconnected table and use it to create a slicer.

Table = ALL(Parameter[Parameter])

Creates a measure to be used as the Y-axis.

Measure = 
SWITCH(MAX('Table'[Parameter]),
"Capacity",SUM(Append1[Capacity]),
"Current", SUM(Append1[Current]),
"Temperature 1", SUM(Append1[Temperature 1]),
"Temperature 2", SUM(Append1[Temperature 2]),
"Temperature 3", SUM(Append1[Temperature 3]),
"Time", SUM(Append1[Time]),
"Voltage", SUM(Append1[Voltage])
)

The measure dynamically displays the corresponding aggregated values based on the selection of the disconnected table slicer.

The final results are as follows. The X and Y axes of the line chart can dynamically change based on the selection of the slicer. It is also possible to filter the tables used in the line chart based on the slicer.

Please see the attached bpix for reference.

Best Regards,
Dengliang Li

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

[barathrk]

Hi @v-denglli-msft ,

Thanks for the help. The snapshots and the sample file provided me a great insight to begin with. There is one modification that I did which I wanted was that, instead of source table on small multiples, I need that in legend, so that the result would be a single line plot with multiple lines from multiple tables. I will work on my large data sample now. Thanks again. 

[grazitti_sapna · Accepted solution]

Hi @barathrk 
In Power BI Desktop, you can plot a multi-line graph from multiple tables dynamically by following these steps:

Step 1: Combine Your Data into a Single Table

Since you have 10 tables with the same structure, the best approach is to combine them into a single table to make visualization easier.

Method 1: Using Power Query (Recommended)

1. Go to Power Query Editor

   • Click on Transform Data in Power BI Desktop.

2. Append Queries

   • Load all 10 tables into Power Query.
   • Select any one of the tables and go to Home > Append Queries > Append Queries as New.
   • Add all 10 tables into the append operation.
   • Click OK → This will create a single table with all your data.

3. Add a "Source Table" Column (Optional but Recommended)

   • If you need to distinguish which row comes from which table, add a custom column that stores the table name.

4. Close & Apply

   • Click Close & Apply to load the combined data into Power BI.

Step 2: Create a Multi-Line Graph

Now that all data is in a single table:

1. Go to Report View and insert a Line Chart.
2. Drag and Drop Fields
   • X-Axis: Choose the desired column (e.g., Capacity or Current).
   • Y-Axis: Choose the desired column (e.g., Voltage).
   • Legend: Use the “Source Table” column (if added) to differentiate the lines.
3. Enable Dynamic Selection of X and Y Axes
   • Create a Disconnected Table with all column names (Capacity, Voltage, Current, etc.).
   • Use Switch Measures to allow users to select X and Y dynamically via slicers.

Step 3: Add Slicers for Interactivity

• Add a Slicer to let users filter out specific tables.
• Add Slicers for dynamic X and Y selection.

Notes:

• No coding required.
• Dynamic selection of X and Y axes.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[barathrk]

Hi @grazitti_sapna,

I cannot understand the step 1-3 add a Source Table column. 

And, since you have mentioned it as optional, I followed all the other steps. Still, it does not work for my data.

[barathrk]

Hi @grazitti_sapna,

I cannot understand the step 1-3 - add a "Source Table" column. 

And, since you have mentioned it as optional, I followed all the other steps. Still, it does not work for my data.

[MFelix]

Hi @barathrk ,

In terms of modelling you need to combine all 10 tables into a single one this can be done using the Power Query, otherwise as you refer you will get to do large amounts of coding to get the correct calculations on PBI, other option can be to have a dimension table that will relate with your tables and you can then plot the chart.

The information that you give here is very scarse so it's difficult to give you a better option.

If you can giv some more context and if possible example and what is the final result it would be easier to point you to the correct option.

[barathrk]

Hi @MFelix ,

Thanks for the reply. 

May I know what kind of additional context should I provide for better guidance?

[MFelix]

Hi @barathrk ,

If you are able to share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files, that would be great if not just a sample of the data itself and what is the result you would like to see.

If the information is sensitive please share it trough private message.

[barathrk]

Sure.