Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

If value not in list, create rows with missing values

This needs a little bit of information.

 

I have a list of user IDs. I also have 20 modules that an user can be associated with, each module with a number of sessions that can be counted.

 

I currently have a table like this:

 

UserIDModuleCount
A13
A23
B12
B32
C13
C23
C33

 

What am I trying to achieve is the following:

 

For every user ID, I need to check if there is one row per module, considering all 20 modules. The way the table is set up right now, when an user has nothing on a module, I don't have a row for the module they are not involved with. If there's a row missing, it should create the row for the missing module and set Count to 0.

 

So assuming the table above only considers 3 modules, I would need to transform it into:

 

UserIDModuleCount
A13
A23
A30
B12
B20
B32
C13
C23
C33

 

How would I got about doing that? 

 

 

 

 

I'm getting my first row by grouping another table per module, where the count is the number of rows on the other table. However this doesn't deal with the missing modules, and I still need to report them as 0.

 

 

Would anyone be able to help me with that?

 

Thank you!

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create an external table with complete UserID and Module:

Capture.PNG 

 

Then merge the two tables using left outer join and expand columns:

2.PNG3.PNG 

 

Finally, replace null with 0:

4.PNG5.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

You can create an external table with complete UserID and Module:

Capture.PNG 

 

Then merge the two tables using left outer join and expand columns:

2.PNG3.PNG 

 

Finally, replace null with 0:

4.PNG5.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuta-msft What if the list of users is not fixated? Its a dynamic list?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.