SOLVED

Help with PowerQuery/Formula to calculate the distinct count of contexts per member

%3CLINGO-SUB%20id%3D%22lingo-sub-2524311%22%20slang%3D%22en-US%22%3EHelp%20with%20PowerQuery%2FFormula%20to%20calculate%20the%20distinct%20count%20of%20contexts%20per%20member%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20Team%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20two%20tables%20-Input%201%20and%20Input%202%20...Table%20Input%201%20has%20the%20membernames%20mapped%20to%20the%20worklists%20-every%20member%20can%20be%20mapped%20to%20multiple%20worklists%20%3B%20Input%202%20table%20has%20the%20contexts%20mapped%20to%20worklists%20-each%20worklist%20can%20have%20multiple%20contexts%20and%20a%20context%20can%20repeat%20across%20multiple%20worklists...%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ENow%20I%20am%20look%20for%20the%20output%20tab%20which%20has%20the%20distinct%20count%20of%20contexts%20at%20a%20member%20level...Can%20you%20please%20help%20me%20with%20a%20power%20query%2Fformula%20for%20this..%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EArun%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2524311%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2524529%22%20slang%3D%22de-DE%22%3ESubject%3A%20Help%20with%20PowerQuery%2FFormula%20to%20calculate%20the%20distinct%20count%20of%20contexts%20per%20member%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524529%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F216929%22%20target%3D%22_blank%22%3E%40Arun%20Chandramouli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20should%20check%20your%20Input1%20Table%2C%20there%20are%20some%20type%20errors%20I%20think.%20otherwise%20it%20would%20not%20fit%20with%20your%20expected%20output.%3C%2FP%3E%3CP%3Eplease%20find%20a%20result%20in%20the%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2526851%22%20slang%3D%22en-US%22%3EBetreff%3A%20Help%20with%20PowerQuery%2FFormula%20to%20calculate%20the%20distinct%20count%20of%20contexts%20per%20member%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526851%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20German_chris!..%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Team,

I have two tables -Input 1 and Input 2 ...Table Input 1 has the membernames mapped to the worklists -every member can be mapped to multiple worklists ; Input 2 table has the contexts mapped to worklists -each worklist can have multiple contexts and a context can repeat across multiple worklists...

Now I am look for the output tab which has the distinct count of contexts at a member level...Can you please help me with a power query/formula for this..

Thanks,
Arun

2 Replies
best response confirmed by Arun Chandramouli (Occasional Contributor)
Solution

@Arun Chandramouli 

 

You should check your Input1 Table, there are some type errors I think. otherwise it would not fit with your expected output.

please find a result in the attached file