Forum Discussion
How to find the rows with missing date range
Here is the solution in Power Query. For each store code it lists all the days that are not covered by any billing period.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BDYAwDAN3ybuSnYTSdJaq+68BFRAEz/Pdw2OIShE1sMJoXLCB+wWz3L6CLX0D4+cjpxMc7K+PFVBBfwJnwifIB9YT5jwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store code" = _t, #"Bill Start Date" = _t, #"Bill End date" = _t]),
#"Locale Start Date" = Table.AddColumn(Source, "Start Date", each Date.FromText([Bill Start Date],"en-gb")),
#"Locale End Date" = Table.AddColumn(#"Locale Start Date", "End Date", each Date.FromText([Bill End date],"en-gb")),
#"Changed Type" = Table.TransformColumnTypes(#"Locale End Date",{{"Start Date", type date}, {"End Date", type date}}),
#"Current Interval" = Table.AddColumn(#"Changed Type", "Interval", each List.Dates([Start Date],Duration.Days([End Date]-[Start Date])+1,#duration(1,0,0,0))),
#"All Store Bills" = Table.AddColumn(#"Current Interval", "Store Bills", each Table.SelectRows(#"Current Interval",(k)=>k[Store code]=[Store code])),
#"Min Date" = Table.AddColumn(#"All Store Bills", "Min Date", each List.Min([Store Bills][Start Date])),
#"Max Date" = Table.AddColumn(#"Min Date", "Max Date", each List.Max([Store Bills][End Date])),
#"Grouped Rows" = Table.Group(#"Max Date", {"Store code", "Min Date","Max Date"}, {"Combined", each List.Combine([Interval])}),
#"Full Period" = Table.AddColumn(#"Grouped Rows", "Full Calendar", each List.Dates([Min Date],Duration.Days([Max Date]-[Min Date])+1,#duration(1,0,0,0))),
Gaps = Table.AddColumn(#"Full Period", "Difference", each List.Difference([Full Calendar],[Combined])),
#"Removed Other Columns" = Table.SelectColumns(Gaps,{"Store code", "Difference"}),
#"Expanded Difference" = Table.ExpandListColumn(#"Removed Other Columns", "Difference")
in
#"Expanded Difference"Hi lbendlin,
I am not sure how to add these transformation steps to a calculated table. I am new user of Power BI. Could you please let me know. I think these steps can only be added in Query editor.
- lbendlin5 years ago
Super User
Yes, in Power Query add a blank source, then open its Advanced Editor and replace the code there with what I posted.
- Anonymous5 years agoNot applicable
The data set I posted on this query is not a real one. There are other columns with Start date, end date all coming from summarised calculated table in PBI desktop. So , I need to perform operation on this calculated table. Is there any ways, I can get this calculated table from desktop to Query editor where I can perform those actions.
Or in the code you have attached, is it possible to change the data source to calculated table ?
- lbendlin5 years ago
Super User
I don't know how to do it in DAX without a UNIONX function.