Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |