Graphing data sets with different numbers of rows on same line plot

[Anonymous]

Hello everybody. I have been trying to find a solution to this problem within PowerBI but I have had no luck. I am trying to create a line chart with many lines where the X axis is time and the Y axis is the values for each data set. Now this is where the problem is, the data is test data that is read from sensors with different read rates. This means that each row does not correspond to the same point in time. Here is an example of how the data looks:

Each column data set (Time N, Data N) could be a different number of rows than any of the other columns (there are some common read rates). Most of the times start around the same time (within 1-2 seconds at most) and end around the same time, hence the different column lengths with different read rates. 

In other programs (python, excel, etc), this is not an issue as I can graph each data pair together on the same graph as everything else (aka each data set is paird like [X Data 1, Y Data 1], [X Data 2, Y Data 2]). However, in PowerBI it seems to assume that every row is related, and so it only allows you to select one thing for the X Data (aka one time column). I am avoiding the use of pre-processing the data outside of excel as I am looking to automate the analysis of new test data (all follows the same format).

How can I handle this in PowerBI? 

EDIT: 

Data example is in the Github repository: https://github.com/Cycon743/PowerBI_Data_Example.git

Sheet 1 contains an example of the data that I will be handling and sheet 2 contains the data made so that it SHOULD be able to be graphed on the same graph in PowerBI since the data is equivalent row-wise aka there is only one time column. Formatting the data like this doesnt have to be the solution if there is a better way out there.

[Ashish_Mathur]

Hi,

This M code will transform the dataset into a 2 column table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Split(Record.ToList(_),2)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    Custom1 = List.Transform(#"Removed Other Columns"[Custom], each Table.FromRows(_)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1", "Column2"}, {"Time", "Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Time", type number}, {"Date", Int64.Type}})
in
    #"Changed Type"

[Anonymous]

Hello Ashish, thank you for your response. Unfortunately this will not work as each of the data sets is different data and needs to be handled seperately. For example, Data 1 could be the voltage of one system, Data 2 could be the mass flow rate of air, Data 3 could be the fuel rate as an example. Therefore, all the data columns cannot be congregated into a single column. 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result clearly.

[Anonymous]

Hi Ashish! Thank you for your reply. I added the file to github, link added to original post. Expected resullt is as described in the original post. The formating of the data doesnt necessarily matter AS LONG AS a graph can be made in which all the data is graphed on one line chart correctly. PowerBI graphs data assuming that a row is consistent across columns (aka, the data row-wise is related to the X-Data column's value in that row), and the current state of the data does not satisfy this. 

BriceRM

[lucadelicio]

Hi @Brice,

the only way is to create a correct dataset to analyze.
You need a single table in wich you have all the attributes.

The only way that i find is to crete manualy with power quey the n table to append to create the dataset.
Ceate a correct dataset for every item:

Append every Item in a total dataset:

Now you can do your analisys correctly:

If you are able to arrive to a complete and correct dataset automatically then you can analyze with powerbi.

As attachments the pbix file!

I hope i'll help you.

[Anonymous]

Hi Luca, Thank you for your reply! This is what I am looking for but I am unsure on how to do this. Could you please explain the steps further or provide resources on the matter? I am confused by the screenshots and how to configure the data to do this. Thank you again for your help!

BriceRM