cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PraveenRaj Regular Visitor
Regular Visitor

Customize line chart

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. 

ThanksChartPowerBI.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Customize line chart

Hi @PraveenRaj

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

you could create a line chart as below

 

5.png 

Best Regards

Maggie

7 REPLIES 7
Community Support Team
Community Support Team

Re: Customize line chart

Hi @PraveenRaj

I can reproduce your problem.

3.png

1.png

 

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]))

2.png

 

Best Regards

Maggie

PraveenRaj Regular Visitor
Regular Visitor

Re: Customize line chart

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

Community Support Team
Community Support Team

Re: Customize line chart

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.

4.png

 

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

 

 

 

Community Support Team
Community Support Team

Re: Customize line chart

Hi @PraveenRaj

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

you could create a line chart as below

 

5.png 

Best Regards

Maggie

PraveenRaj Regular Visitor
Regular Visitor

Re: Customize line chart

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

line.PNG

Community Support Team
Community Support Team

Re: Customize line chart

Hi @PraveenRaj

Update!

Please refer to my last 2 post.

 

Best Regards

Maggie

Community Support Team
Community Support Team

Re: Customize line chart

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.