Aggregation of data on Unique Values and reporting it location-wise

[CMMaliniJoshi]

Dear All
I request you to help me with a solution for below mentioned details:
1. A customer is having a Legal Identity Number. Against this number there has to be client number and it has to be only one number against one legal id number. But I have got data where there are multiple client numbers against single legal id number. Hence, I have to analyze the data and provide details of Legal Id numbers having more than one client number.
2. For every customer number there are ledger numbers against that customer number and there can be more than one ledger number for single customer number.
3. There are different locations and the ledger numbers are at different locations. So the multiple customers numbers can be spread across different locations.
4. I have to prepare the data location-wise in which I have to provide to each location
- Details of Legal Ids having multiple customer numbers and their respective ledger numbers
- And details of other customer numbers and other ledger numbers for that legal id at other locations
5. I can not provide actual data but I am providing herewith hypothetical data and desired output. For the desired Output, two columns are marked as Option-A and Option-B alongwith other desired columns. I have been trying for Option-B type data. But if it becomes very complicated, I will have to try for data as per Option-A. I have to make use of Power Query only as I have very old version of excel in my company (Excel 2013 standard edition with only 2 GB RAM) and I do not have Power Pivot / DAX functionality. My actual data is running in approximately 150000 rows. I am very new for power query. Hence pl forgive me for my low knowledge.
6. I can work out legal ids having multiple customer numbers with the distinct count of customer numbers with the help of Table.Group. But my problem is I dont know how to report it location-wise as some customers' customer numbers/ledger numbers can be at different locations.
Hence, please help me /guide me with Power Query solution for this situation.

Regards

 

[p45cal]

I'm not at all sure I've got this right, but have a look at the green table at cell A29. I've added a couple of notes in cells C20 and C21.
Also a standard pivot which probably isn't much use at cell I2

 

[CMMaliniJoshi]

Dear p4cal

Thanks a lot for helping me.

Kindly note that as pointed out by you in Cells C20, Customer No C3 is not found at Location Code 1. But I have to provide the details of Customer No C3 to Location Code 1 because there is a case of Multiple Customer No C3 against Legal Id No A1 at a Location Other than Location Code 1.
Similarly, as pointed out by in Cell C21, Customer No C1 is not found at Location Code 2. But I have to provide the details of Customer No C1 to Location Code 2 because there is case of Multiple Customer No C3 against Legal Id No A1 at a Location Other than Location Code 2.
Therefore, I have included these two in my desired output.

As requested in my first post at Point No 4
- And details of other customer numbers and other ledger numbers for that legal id at other locations

I am required to report details of multiple Customer Nos and Ledger Numbers for a particular Legal Id to all of the branches involved even if a particular Customer No is not found at that branch. The reason is that Customer Number is not found at that branch but the Legal Id under question is found that branch.

I have given example of just two branches having Legal Id No A1. In my actual data there are numerous instances where a Legal Id number is found at even more than two branches. Therefore, I have to report all the branches about all the Customer Numbers (and their ledgers) against that Legal Id.

There are even instances in my actual data where only one customer number is found at one location and another only one customer number is found at second location. In such case, if I give details of only each 1 customer number to respective location, they will not understand why they are given that case even if there is only one customer number at their location.

Therefore, I shall be thankful if you can help me with desired output. Again I am thankful for taking interest and helping me.

Regards

 

[p45cal]

I have got data where there are multiple client numbers against single legal id number

Is there ever the reverse, where the same Customer Number could have multiple Legal Ids?

all of the branches

=all of the Location_Codes ?

 

[CMMaliniJoshi]

Dear p45cal

Please find appended below my reverts:

Q: Is there ever the reverse, where the same Customer Number could have multiple Legal Ids?
Revert: No. There is no such case with multiple Legal IDs against single Customer No.

Q:all of the branches
Revert: I am sorry, it is my mistake. Please consider "branches" replaced with "Location Code"

Regards

 

[p45cal]

Try the attached.
It mimics your required output.
One thing I haven't done is to cater for multiple names existing for a given customer number. It's easy enough to list them like has been done for alternative customer numbers and ledger numbers, just I haven't done it. At the moment if there are more than one customer names for a given Customer Number they get added as separate lines, everything else on those lines remaining the same.

There are far too many queries in the attached; I'm sure that I don't need as many and that it can be much slicker, but as I was bumbling through putting this together to give you a working solution sooner rather than later, I took only 'baby' steps. Maybe if I get the time and the inclination I'll have a shot at streamlining it
…unless, of course, it doesn't work properly.

 

[CMMaliniJoshi]

Dear p45cal

The output is exactly as required. Just now I am at my home. Hence, I shall run this query on my actual data in the morning after incorporating necessary changes in these queries as per field names etc. of actual data. Accordingly, I shall get back to you with outcome as to whether result (output of these queries) is proper on actual data. However, since, I am very much new to Power Query I may need some time to prepare actual queries as per your guidance queries. So kindly bear with me till that time period.

In the mean time, kindly guide me on 2 points -
1. When I open Power Query editor I get a message of COMPATIBILITY WARNING that "The queries in this workbook might be incompatible with your current version of excel. These queries were authored with a newer version of Excel or Power Query and might not work in your current version." Kindly guide if this will create a problem when I open this Workbook tomorrow in my Company's computer because it is having very old version (Excel 2013 standard edition - 2 GM RAM).
2. When I open the Query Named "Landing2OnlyLegalIDsWithMultipleCNs", the second step is named as "Navigation". However, when I open it in Advanced Editor it shows the step name as "LegalIDNoswithmorethan1CN". Besides the same step name has been used as a LIST in List.Contains in next step to Filter data of of table named "Source" i.e. first step of the query. Hence, kindly guide why two different names appear.

Again, I repeat my thankfulness for taking such a keen interest regarding my requirement.

Regards

 

[p45cal]

I get a message of COMPATIBILITY WARNING that "The queries in this workbook might be incompatible

might is the operative word. Wait and see if an error occurs; often it's just a trivial change needed.

kindly guide why two different names appear

I don't really know! We can probably get it to show the right step names by moving lines around in Advanced Editor (often, NOT just by dragging the steps up/down)

 

[CMMaliniJoshi]

Dear p45cal
You will be glad to know that the solution provided by you is working correctly on my actual data. I am really thankful for your support. It has really helped me to solve an issue standing for a long time.
Thanks and regards