Customize line chart

[PraveenRaj]

Hi everyone, i want to display multiple meter readings in single line chart, these meter readings are logged in seperate tables. I created relation to each tables via time created .Randomly some time data will not logged, so some of the cells are empty. If i add those data to chart. it looks lot of incomplete lines in my chart. I need to join those incomplete lines to next point automatically, is that possible? If that possible share with me how to do it. 

Thanks

[v-juanli-msft · Accepted solution]

Hi @PraveenRaj

after steps in the edit queries,  close&&apply, 

you could create a line chart as below

 

Best Regards

Maggie

[PraveenRaj]

It is the live data, so i'm using direct query, I'm unable to match the datecreated and column i'm created, 0 matches are found

[v-juanli-msft]

Hi @PraveenRaj

Does my reply slove your problem?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @PraveenRaj

Update!

Please refer to my last 2 post.

Best Regards

Maggie

[v-juanli-msft]

Hi @PraveenRaj

I can reproduce your problem.

Then i create measures in each table

Measure 1 = IF(MAX(Sheet1[value1])=BLANK(),0,MAX(Sheet1[value1]))

Measure 2 = IF(MAX(Sheet2[value])=BLANK(),0,MAX(Sheet2[value]))

Best Regards

Maggie

[PraveenRaj]

Thanks for your answer it is working, But instead of making it as zero, is it possible to set previous or next value to it?. If that is possible , it will be more helpful for me.

Thanks

[v-juanli-msft]

Hi @PraveenRaj

This need to work with another method

open the edit queries, 

1.

create a new blank query renamed as "calendar" ,

open the advanced editor, paste the code below

Source = List.Dates(#date(2018, 1, 1), 3, #duration(1, 0, 0, 0))

then convert the list to a table

supplementary instruction:

create a new blank query "Query1", paste the code in advanced editor to create a list of time from 12:00:00 am to 11:59:00pm

let
    Source = List.Times(#time(0, 0, 0), 60*24, #duration(0, 0, 1, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "time"}})
in
    #"Renamed Columns"

in "calendar" table, create a "Custom column" named "Custom", paste the code:

Query1[time]

then "Expand to new values",

next, merge column "date" and "Custom" to the same format as datetime column in other tables. 

eg. using merge with seperator space

1/1/2018 12:00 AM

2.merge the "calendar" with "Sheet1" and "Sheet2", then select "value" column to expand.

for example, in "calendar" table, merge the "Sheet1" table.

3.finally fill down for "value1","value2" column

Whole code in advanced editor of  "calendar" table

let
 Source = List.Dates(#date(2018, 1, 1), 3, #duration(1, 0, 0, 0)),
 #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "date"}}),
 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Query1[time]),
 #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
 #"Inserted Merged Column" = Table.AddColumn(#"Expanded Custom", "Merged", each Text.Combine({Text.From([date], "en-US"), Text.From([Custom], "en-US")}, " "), type text),
 #"Renamed Columns1" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "datetime"}}),
 #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"datetime", type datetime}}),
 #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"datetime"},Sheet1,{"date"},"Sheet1",JoinKind.LeftOuter),
 #"Expanded Sheet1" = Table.ExpandTableColumn(#"Merged Queries", "Sheet1", {"value1"}, {"Sheet1.value1"}),
 #"Merged Queries1" = Table.NestedJoin(#"Expanded Sheet1",{"datetime"},Sheet2,{"date"},"Sheet2",JoinKind.LeftOuter),
 #"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged Queries1", "Sheet2", {"value"}, {"Sheet2.value"}),
 #"Filled Down" = Table.FillDown(#"Expanded Sheet2",{"Sheet1.value1", "Sheet2.value"})
in
 #"Filled Down"

Best Regards

Maggie