Forum Discussion
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.
Thanks
Hi PraveenRaj
after steps in the edit queries, close&&apply,
you could create a line chart as below
Best Regards
Maggie
7 Replies
Hi PraveenRaj
after steps in the edit queries, close&&apply,
you could create a line chart as below
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
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
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.
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
