Styjun

How To Make Earth's Oceans as Brackish as Lyr's

What is this WWII four-engine plane on skis?

Should I inform my future product owner that there are big chances that a team member will leave the company soon?

Wouldn't Kreacher have been able to escape even without following an order?

Can Brexit be undone in an emergency?

Why are there no programmes / playbills for movies?

Can an infinite series be thought of as adding up "infinitely many" terms?

Permutations in Disguise

How would you translate Evangelii Nuntiandi?

Transit visa to Hong Kong

What was the ultimate objective of The Party in 1984?

What does the "capacitor into resistance" symbol mean?

Why does JavaScript convert an array of one string to a string, when used as an object key?

Python web-scraper to download table of transistor counts from Wikipedia

Why does '/' contain '..'?

'Overwrote' files, space still occupied, are they lost?

What did the first ever Hunger Games look like?

Answer Not A Fool, or Answer A Fool?

How to make classical firearms effective on space habitats despite the coriolis effect?

Pronunciation of "солнце"

How often is duct tape used during crewed space missions?

Does Forgotten Realms setting count as “High magic”?

Asked to Not Use Transactions and to Use A Workaround to Simulate One

In Bb5 systems against the Sicilian, why does White exchange their b5 bishop without playing a6?

If all values in list are not present, create rows with missing values

SharePoint export to excel filtered list data based on row level securityPower Query - M “Expression.Error: A cyclic reference” when merging with staging tableDynamic Grouping in Power BI/PowerPivot Model based on a calculated fieldHow to compensate for general demographics in PowerBI map visualizations?Create and Expand the column with all records.Power BI - Finding records within time span of another recordReferencing a table given in column as textPowerBI Count all Rows With Value In GroupLooking to get count of ID for entity that has an answer to two separate questions, in the same row - Power BIPower Query; Calculating when a certain value in numerical order is missing and creating an additional row

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

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:

UserID Module Count 
A 1 3 
A 2 3 
B 1 2 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

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:

UserID Module Count 
A 1 3 
A 2 3 
A 3 0 
B 1 2 
B 2 0 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

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.

powerbi powerquery

share|improve this question

asked Mar 28 at 13:00

Ana Victória LadeiraAna Victória Ladeira

2511 silver badge55 bronze badges

add a comment
 | 

0

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:

UserID Module Count 
A 1 3 
A 2 3 
B 1 2 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

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:

UserID Module Count 
A 1 3 
A 2 3 
A 3 0 
B 1 2 
B 2 0 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

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.

powerbi powerquery

share|improve this question

asked Mar 28 at 13:00

Ana Victória LadeiraAna Victória Ladeira

2511 silver badge55 bronze badges

add a comment
 | 

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:

UserID Module Count 
A 1 3 
A 2 3 
B 1 2 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

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:

UserID Module Count 
A 1 3 
A 2 3 
A 3 0 
B 1 2 
B 2 0 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

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.

powerbi powerquery

share|improve this question

asked Mar 28 at 13:00

Ana Victória LadeiraAna Victória Ladeira

2511 silver badge55 bronze badges

UserID Module Count 
A 1 3 
A 2 3 
B 1 2 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

UserID Module Count 
A 1 3 
A 2 3 
A 3 0 
B 1 2 
B 2 0 
B 3 2 
C 1 3 
C 2 3 
C 3 3 

share|improve this question

asked Mar 28 at 13:00

Ana Victória LadeiraAna Victória Ladeira

2511 silver badge55 bronze badges

share|improve this question

asked Mar 28 at 13:00

Ana Victória LadeiraAna Victória Ladeira

2511 silver badge55 bronze badges

asked Mar 28 at 13:00

Ana Victória LadeiraAna Victória Ladeira

2511 silver badge55 bronze badges

asked Mar 28 at 13:00

Ana Victória LadeiraAna Victória Ladeira

2511 silver badge55 bronze badges

1 Answer
1

active

oldest

votes

0

You could try:

NewTable = 
VAR T1 = SUMMARIZECOLUMNS ( Table1[UserID] )
VAR T2 = SUMMARIZECOLUMNS ( Table1[Module] )
RETURN
 ADDCOLUMNS ( 
 CROSSJOIN ( T1, T2 ),
 "Count",
 LOOKUPVALUE ( 
 Table1[Count], 
 Table1[UserID], [UserID],
 Table1[Module], [Module]
 ) + 0
 )

See https://pwrbi.com/so_55398321/ for a worked example PBIX file

share|improve this answer

answered Mar 28 at 13:33

OllyOlly

5,70911 gold badge1212 silver badges3030 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That solved it! Thank you @Olly
  
  – Ana Victória Ladeira
  Mar 28 at 14:06
  

  
  
  
  

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55398321%2fif-all-values-in-list-are-not-present-create-rows-with-missing-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

You could try:

NewTable = 
VAR T1 = SUMMARIZECOLUMNS ( Table1[UserID] )
VAR T2 = SUMMARIZECOLUMNS ( Table1[Module] )
RETURN
 ADDCOLUMNS ( 
 CROSSJOIN ( T1, T2 ),
 "Count",
 LOOKUPVALUE ( 
 Table1[Count], 
 Table1[UserID], [UserID],
 Table1[Module], [Module]
 ) + 0
 )

See https://pwrbi.com/so_55398321/ for a worked example PBIX file

share|improve this answer

answered Mar 28 at 13:33

OllyOlly

5,70911 gold badge1212 silver badges3030 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That solved it! Thank you @Olly
  
  – Ana Victória Ladeira
  Mar 28 at 14:06
  

  
  
  
  

add a comment
 | 

0

You could try:

NewTable = 
VAR T1 = SUMMARIZECOLUMNS ( Table1[UserID] )
VAR T2 = SUMMARIZECOLUMNS ( Table1[Module] )
RETURN
 ADDCOLUMNS ( 
 CROSSJOIN ( T1, T2 ),
 "Count",
 LOOKUPVALUE ( 
 Table1[Count], 
 Table1[UserID], [UserID],
 Table1[Module], [Module]
 ) + 0
 )

See https://pwrbi.com/so_55398321/ for a worked example PBIX file

share|improve this answer

answered Mar 28 at 13:33

OllyOlly

5,70911 gold badge1212 silver badges3030 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That solved it! Thank you @Olly
  
  – Ana Victória Ladeira
  Mar 28 at 14:06
  

  
  
  
  

add a comment
 | 

You could try:

NewTable = 
VAR T1 = SUMMARIZECOLUMNS ( Table1[UserID] )
VAR T2 = SUMMARIZECOLUMNS ( Table1[Module] )
RETURN
 ADDCOLUMNS ( 
 CROSSJOIN ( T1, T2 ),
 "Count",
 LOOKUPVALUE ( 
 Table1[Count], 
 Table1[UserID], [UserID],
 Table1[Module], [Module]
 ) + 0
 )

See https://pwrbi.com/so_55398321/ for a worked example PBIX file

share|improve this answer

answered Mar 28 at 13:33

OllyOlly

5,70911 gold badge1212 silver badges3030 bronze badges

NewTable = 
VAR T1 = SUMMARIZECOLUMNS ( Table1[UserID] )
VAR T2 = SUMMARIZECOLUMNS ( Table1[Module] )
RETURN
 ADDCOLUMNS ( 
 CROSSJOIN ( T1, T2 ),
 "Count",
 LOOKUPVALUE ( 
 Table1[Count], 
 Table1[UserID], [UserID],
 Table1[Module], [Module]
 ) + 0
 )

share|improve this answer

answered Mar 28 at 13:33

OllyOlly

5,70911 gold badge1212 silver badges3030 bronze badges

answered Mar 28 at 13:33

OllyOlly

5,70911 gold badge1212 silver badges3030 bronze badges

answered Mar 28 at 13:33

OllyOlly

5,70911 gold badge1212 silver badges3030 bronze badges