cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PraveenRaj
Helper I
Helper I

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
v-juanli-msft
Community Support
Community Support

Hi @PraveenRaj

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

you could create a line chart as below

 

5.png 

Best Regards

Maggie

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

Hi @PraveenRaj

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

you could create a line chart as below

 

5.png 

Best Regards

Maggie

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

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.

Hi @PraveenRaj

Update!

Please refer to my last 2 post.

 

Best Regards

Maggie

v-juanli-msft
Community Support
Community Support

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

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

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

 

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.