Forum Discussion
How to find the rows with missing date range
Thanks for the solution. I am Okay to make changes in the power query editor as well. But the columns I mentioned are comming from summarised calculated table from other tables. So that calculated table is not visible to make any changes in Query editor. If so, then please suggest the other solution as well. However, I'll try this DAX you have suggested above and let you know of the outcome.
My DAX is not a solution. I demonstrated where I got stuck when trying to achieve a DAX based solution.
If the columns come from a calculated table they can also come from Power Query. There's not a lot in DAX that Power Query couldn't do (except for the measure thing 🙂 )
- Anonymous5 years agoNot applicable
Sure. If so, could you please suggest the solution in Power query. I can try that as well and see if it works.
Thanks for you help
- lbendlin5 years ago
Super User
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"- Anonymous5 years agoNot applicable
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.