i have a start date and end date

[Anonymous]

Hi All,

I have a set of data for particular month say August (31 days)

Below is an example:

Start Date               End Date              Data 

01/08/18 10:41	03/08/18 12:15	2
06/08/18 9:00	07/08/18 15:00	1

So now i want to represent this data for 31 days the dates being horizontally represented in Power BI.

Date   1 2 3 4 5 6 7 8 9 10 . . . . . 

Data   2 2 2 0 0 1 1 0 0 0  . . . .

If the date is not availabe in the data set the value should be 0.

Can you please help me how this can be calculated?

Regards,

Nagesh

[v-qiuyu-msft]

Hi @Anonymous,

You can create a list of date for August in year 2018: 

let
    Source = List.Dates(#date(2018, 8, 1), 31, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Then in another table, add a custom column to list all dates between Start Date and End Date. 

= Table.AddColumn(#"Renamed Columns", "DateUpdate", each List.Transform({Number.From([StartDate])..Number.From([EndDate])}, each Date.From(_)))

Then merge these two tables. Whole Power Query is: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUN7DQN7RQMDSwMjFU0lEyMIYJGFkZmgIFjJRidYDqzKDCllYGBiBl5jBlphABQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}, {"Data", Int64.Type}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Start Date]), type date),
    #"Inserted Date1" = Table.AddColumn(#"Inserted Date", "Date.1", each DateTime.Date([End Date]), type date),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Date1",{{"Date", "StartDate"}, {"Date.1", "EndDate"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "DateUpdate", each List.Transform({Number.From([StartDate])..Number.From([EndDate])}, each Date.From(_))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "DateUpdate"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom",{"DateUpdate"},Query1,{"Column1"},"Query1",JoinKind.RightOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Column1"}, {"Query1.Column1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Query1",{{"Query1.Column1", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "DataUpdate", each if [DateUpdate] = [Query1.Column1] then [Data] else 0)
in
    #"Added Custom1"

You can look into attached pbix file. 

Best Regards,
Qiuyun Yu