Forum Discussion
Automaticly generate missing rows
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.
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
4 Replies
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
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 ?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])
