Forum Discussion
Dates on a graph - continuous -adding a date dimension
OK solved
- what are we trying to achieve..show a value on a graph of zero, for days that have no data or events.
How to achieve ..basically ..create a list of all calender dates for the period of data in question and the left join that to your data
Note, there is a power query M function to create a list of dates List.Dates ( date dimension)
Note, start DATE in this case 2015,12,7, Determine your earliest date in your data.
This will create a list of dates with no future dates !
Step 1
in The query Editor create a blank query
and paste the below into the advanced editor.
let Source = #date(2015,12,7), #"Converted to Table" = #table(1, {{Source}}), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Date", each List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start Date"}}) in #"Renamed Columns"2) Query Editor create another query as the left outer join (merge ) your "Date" list column with a date in your data which has no data on some days.
3) Query editor create a custom column called 'Data Exists' with this formula, where ID is column can be null on some dates.
if [ID] is null then 0 else 1
4) change type to whole number. Must be a number or it dosen't work.
5) create a graph put "date" on the x-axis.
6) the column 'Data Exists' should have a sigma sign on meaning it is a number. Drag on the value axis and it will sum these.
Graph will now go to ZERO where there are gaps - SIMPLE ?
BEFORE
AFTER
Graph is continious too..
I have discovered the issue but can't work around it. The continious date field is type text when M function creates the table.
And then you left join that data to the real data. You look in the visual graph its there ..as text type not date , so the graph is out of order as it is text sorting, So you change the column type to Date and graph only show the inner join data..
still working on it..
OK solved
- what are we trying to achieve..show a value on a graph of zero, for days that have no data or events.
How to achieve ..basically ..create a list of all calender dates for the period of data in question and the left join that to your data
Note, there is a power query M function to create a list of dates List.Dates ( date dimension)
Note, start DATE in this case 2015,12,7, Determine your earliest date in your data.
This will create a list of dates with no future dates !
Step 1
in The query Editor create a blank query
and paste the below into the advanced editor.
let Source = #date(2015,12,7), #"Converted to Table" = #table(1, {{Source}}), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Date", each List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start Date"}}) in #"Renamed Columns"2) Query Editor create another query as the left outer join (merge ) your "Date" list column with a date in your data which has no data on some days.
3) Query editor create a custom column called 'Data Exists' with this formula, where ID is column can be null on some dates.
if [ID] is null then 0 else 1
4) change type to whole number. Must be a number or it dosen't work.
5) create a graph put "date" on the x-axis.
6) the column 'Data Exists' should have a sigma sign on meaning it is a number. Drag on the value axis and it will sum these.
Graph will now go to ZERO where there are gaps - SIMPLE ?
BEFORE
AFTER