Conditional column evaluation on grouped data to maintain a distinct list

[ctaylor]

Hello,

Using PowerBI I am trying to use Active Directory as the source of user information for our mobile phone accounts because the data in the mobile environment has gotten way out of sync and is causing billing issues. 

You dont have to tell me that this is a silly idea, I've already tried to explain so here I am with a problem.

There is a big disconnect in data and in order to not drop rows between the two sets of data I will have instances where multiple users (past and present associates) may have the same phone number or no number at all.  The joining of data will be to dump all the numbers from the mobile provider, because that's what is being billed on, and attaching the information from AD of the associate that matches.  

So, I need to build a 1:1 lookup with no good scalar to use.  I have decided that the best way would be to build the distinct list of mobile numbers, join the displayName and userAccountStatus from AD, then group on mobile number and also added a count for this post. Results in:

Inside those grouped records are a UserAccountControl value of 512 or 514. 

512 = Active and 514 = Disabled

See for reference: https://www.windowstechno.com/useraccountcontrol-attribute-flag-values/

So, this brings me to the end of my PowerQuery knowledge. What I want in the end is a table with 1 Mobile Number for 1 person (or nobody).  If the count of users with that number is 1 then I dont care if its a 512 or a 514 user, I want that record expanded.  If the count is > 1 then I want the 512 record only.  If there are multiple 512 records then we get to the point where my boss and I butt heads because I can't think of another scalar value I would use to determine the record to choose! But, I guess I would rather error handle that and return "Multiple" or something just to keep the distinct list of mobile numbers.

Here is a quick pbix file that I whipped up with just the attempt to group up the data.

https://wallick-my.sharepoint.com/:u:/p/ctaylor/EYK2cvHHo4ZKuVEd3S0IWLUBbNJaN287ifSiVsZhzRclRA?e=15u...

[ctaylor]

Bump because I am still stuck trying to figure out how to do the inner evaluations.

[ctaylor]

Bumping and adding new code as I am getting closer but I've again run into the edge of my knowledge.

I am producing a table of distinct wireless numbers and then selecting the appropriate employeeID to return and then relating that back to the AD data to produce a name and other information.

Here's where I am:

{"Wireless Number"}, 
{
{"Items", each _, type table [Wireless Number=nullable text, GetBaseUserInfo.displayName=nullable text, GetBaseUserInfo.Custom.extensionAttribute15=nullable text, GetBaseUserInfo.UserStatus.user.userAccountControl=nullable number]}, 
{"Count of Users", each Table.RowCount(_), Int64.Type}
,{"EmpID", 
    each 
        if Table.RowCount(_) = 1 
            then List.Max([GetBaseUserInfo.employeeID])
        else if Table.RowCount(_) > 1 then
            each if [GetBaseUserInfo.UserStatus.user.userAccountControl] = 512
                then List.Max([GetBaseUserInfo.employeeID])
                else List.Max([GetBaseUserInfo.employeeID])
        else 0, Int64.Type
    }
}

I feel like my issue is getting closer to resolve but the step where I am trying to look into the grouped data to find a value seems to produce an output that says "Function".  Obviously I am doing something wrong, so perhaps someone can help correct me.

The logic I want is if the rowcount is 1 then give me the empID.

If the rowcount is > 1 then give me the user empID where the status is 512

If the rowcount is > 1 and there are multiple 512 users (this case exists currently due to user error) then give me the user with the higher empID

The else then is if the rowcount is > 1 and there are no 512 users then give me the 514 user with the highest empID

Please help with any insights or tips on how to properly perform this set of nested if statements in this language that I don't fully understand. 

Thanks

[lbendlin]

You have nested each statements. That can confuse the query engine, and general advice is to use explicit calls instead of the syntax sugar.

[ctaylor]

Please...I have stated that I am trying to make something work in a language that I can use in GUI form but once we leave that realm and move to the advanced editor I am lost.  Telling me I did something wrong without providing some syntax to correct it seems like trolling at this point.

What do I need to do to be able to evaluate the inner items of the grouped rows?

Again, here is my pseudocode: 

The logic I want is if the rowcount is 1 then give me the empID.

If the rowcount is > 1 then give me the user empID where the status is 512

If the rowcount is > 1 and there are multiple 512 users (this case exists currently due to user error) then give me the user with the higher empID

The else then is if the rowcount is > 1 and there are no 512 users then give me the 514 user with the highest empID

Currently the inner evaluations are not happening, hence why i tried another each statement because I simply don't know what I need to do to evaluate the inner items to pick the result I am looking for.  That's what I need help with!

[lbendlin]

Nice problem 🙂  Do you have any sort of "Last updated date" that could break the 512 ties?

Also, instead of merging the tables like that I think you will want to do  a full outer join first, just in case.  Maybe you are doing that already - can you show the Table.Combine code?

[ctaylor]

@lbendlin Thanks for your interest in my problem here!

I have thought about using a last modified/logon/etc type of date but of those I thought of, I couldn't think of one that wouldn't introduce a bug that could shift the output of the report based on when it was viewed.  In our actual live data there is are two active associates with the same mobile number in AD.  This is obviously an error we need to fix, but what could happen to the report if it weren't?  If user A is the one with the MAXed value one day but something occurs that shifts the value for User B so that they are the one being pulled from the two then based on when you looked at the report you could see a different name associated.  So, I would rather error handle that to alert someone that seeing "Multiple" means that there is data that needs to be corrected.  But I think while that logic applies to the active user, what if I have 2 users on a line that are Inactive?  I would actually want to use the Last Modified field to display the most recent active user.

I know the logic that I need to apply my problem is that I simply do not know how to do it in this application, and that's what I am looking for help with.

Here's the code for creation of this table currently.

let
    Source = VZW_Wireless_Summary,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Wireless Number"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Wireless Number", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Wireless Number"}, GetBaseUserInfo, {"Formatted Mobile"}, "GetBaseUserInfo", JoinKind.LeftOuter),
    #"Expanded GetBaseUserInfo" = Table.ExpandTableColumn(#"Merged Queries", "GetBaseUserInfo", {"displayName", "Custom.extensionAttribute15", "UserStatus.user.userAccountControl"}, {"GetBaseUserInfo.displayName", "GetBaseUserInfo.Custom.extensionAttribute15", "GetBaseUserInfo.UserStatus.user.userAccountControl"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded GetBaseUserInfo",null,"Missing",Replacer.ReplaceValue,{"GetBaseUserInfo.displayName"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Missing",Replacer.ReplaceValue,{"GetBaseUserInfo.Custom.extensionAttribute15"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","","Blank",Replacer.ReplaceValue,{"GetBaseUserInfo.Custom.extensionAttribute15"})
in
    #"Replaced Value2"

The reason I am doing a left outer is because this report is about billing and I only care about those numbers only. AD contains non-company mobile numbers so adding all those records in doesn't seem like it would be beneficial unless I am missing something you could explain further?

[lbendlin]

What you describe makes sense.  Maybe have a matrix as a result with four buckets.  Active/Inactive and Single/Multiple  for x and y axis. Like a magic quadrant chart 🙂

[ctaylor]

@lbendlin 

I'm confused by your response. 

On one hand, that concept could be helpful for the side project to fix up the gaps in AD data that I know exist, but I am not looking for a visual answer. I am looking for code help to be able to parse through the grouped records to create a 1:1 table of data.

Thanks

[lbendlin]

I have found through the years that visual solutions are often much easier to achieve than formula based solutions. In other words - I have no idea how your formula could possibly be made to work 🙂  I am sure the experts here will have something that can help you along.