Automaticly generate missing rows

[BoyerAlexis]

Hi,

I'm facing an issue at work.

I've got a table with monthCode, city, action as columns, like so :

MonthCode	City	Actions
1	Dijon	5
1	Nevers	2
1	Autun	8
2	Dijon	8
2	Autun	5
3	Dijon	11
3	Nevers	1
3	Autun	14

As you can see, Nevers onlys has records for the 1st & 3rd month but not the second.
I'd like to automaticly generate a row like this :

2

Nevers

0

Do you guys've got any idea if this is possible and give me a lead on this ?

I've tried something with powerquery & the ADDMISSINGITEM thingy with SUMMARIZECOLUMNS but it didn't work, as expected.

[FarhanAhmed · Accepted solution]

If you want to view records that are no available for months you can do following

- Create a separate table for City

- Create a separate table for MonthCode

- Join Both table with this main table

- Use City & MonthCode from those new tables in Table Visual

- Use Actions from main table

- in Table Visual fields right click on city and check "Show Items with no data"

- you will get following results  as show in Table 2

[BoyerAlexis]

Thanks a lot for your answer Ahmed !

I've managed to join my city and monthCode tables like this

But, this might seems dumb, how do i join my main table with this one ?

[FarhanAhmed]

you need to create seperate table for each of Month Code & City and joined them in Data Model as shown below

I have created those tables using simple approach of summarize on Main table

City = SUMMARIZE(Table,Table[City])

[BoyerAlexis]

This is exactly what we needed, thanks a lot Ahmed !