added missing rows to table

[Anonymous]

i have a  table with date and count in Power Query. after the starting date, each week has a new count.

for this table for some weeks count and date are missing.(ex: March 6th,march 20th,April 3rd and etc). so what i need to do is  add rows when the weeks missing with date and count as 0. This is not static table when the data changes this table have to change accordingg with missing weeks and count as 0's.

[Anonymous]

Hi @Anonymous ,

You could create a table with below formula.

New_Table = 
var _calendar = CALENDAR(MIN('Table'[start of week]),MAX('Table'[start of week]))
return
ADDCOLUMNS(_calendar,"count",LOOKUPVALUE('Table'[count],'Table'[start of week],[Date])+0)

Best Regards,

Jay

[dhruvinushah]

@Anonymous Hi, 

can you please share the query steps or the M code in the advanced editor being used to understand why the table is not showing rows for the missing weeks? 

Thanks !

[Anonymous]

There is no data for that weeks.  but when comes to visualizations using line chart I need to show every week's data even if the weeks have 0 counts.  ex: first week's count is 10 and last week's count is 5. line chart adds a line between two weeks. but the correct way is 10 >0>0>5 instead of 10>5

[dhruvinushah]

Hi @Anonymous , Got it, thanks for explaining that. 

If your report model has a date table that has all the dates for your report I would suggest setting up a relationship between the DateTable[Date] and your above table's [StartOfWeek] date column. 

It would be a Many to One relationship going from Date Table ( * -> 1 ) to your above table. 

You can easily create a seperate date table using CALENDARAUTO function to populate it. 
Here's a link for detailed steps: Creating a Power BI Calendar Table Using DAX Functions: Syntax, Examples & 3 Easy Steps - Learn | Hevo (hevodata.com)

Once you have completed setting up the relationship between the two tables, go ahead and use the Date from the Calendar table you just created in your chart to visualize the count column. 

Hope this helps, tag me (@) if you need more help on it. Feel free to mark the answer as a solution if it helped you. 

Thanks!