Iterate over same table to summarize (parent-child like relationship) based in duplicates

I have a table with N Columns where I have (among others) the following columns and conditions
A) There are 3 executing/managing sites (IN-HOUSE)
B) There are 4 outsourcing sites (OFFSHORE)
C) For every offshore Project, there must always be an IN-HOUSEManaging site

1) DEPARTMENT - Contains Coding/Managing site (Either in-house or Offshore) - IN-HOUSE_A,IN-HOUSE_B, IN-HOUSE_C, OFFSHORE_A, OFFSHORE_B, OFFSHORE_C, OFFSHORE_D
2) Project_Name
3) Plan_Date

a) If the project is being Managed and Coded 100% in-house in one SINGLE SITE, then there is just ONE record for that PROJECT in that DEPARTMENT in a certain PLAN_DATE, e.g.: DEPARTMENT = IN-HOUSE_A - (B or C)
b) If the project is being Managed and Coded 100% in-house in more than one Executing Site, then there is ONE record for each Executing Site in the DEPARTMENT column for that PROJECT in a certain PLAN_DATE (in this case, it could be considered as separate projects since Budgeting is done at the Executing/Managing Site level)
c) If a Project is sent offshore (it can be sent to 1 or more off-shore Coding sites), there would be two or more records
i. One record with DEPARTMENT = "IN-HOUSE_A" -(B or C)
ii. One record with DEPARTMENT = "OFFSHORE_A" -(B, C or D) for each off-shore site where code is being done

I want to find a way in DAX (or Power Query) to add a CUSTOM COLUMN in that table to indicate the Managing Site for each outsourced project for easier reporting and filtering, e.g.:
a) Cost plan by Executing site for 100% managed projects
b) Cost Plan by Coding site for outsourced projects
c) Cost Plan by Managing Site-Coding Site

NOTES:
To detect Managing Site, I was thinking on locate DUPLICATES (PROJECT_DATE + PLAN_DATE) then:
i. locate the DEPARTMENT value for record containing DEPARTMENT = IN-HOUSE_X (A, B, or C) value
ii. Locate it's associated records (with same PROJECT_DATE + PLAN_DATE) but DEPARTMENT = OFFSHORE_X (A, B, or C) and copy the IN-HOUSE department from the previous record to the Custom column, understanding that can be 1 or more offshore records||
iii. For unique records, I'd add a custom label (e.g.: "100% IN-HOUSE") or leave it blank in that Custom column

I can't change the design on the table since it comes from a C.A. Clarity PPM feed

ImkeF

10 years ago

You can check out this M-code:

let

    qSourceData= Table.PromoteHeaders(Table.FromColumns({ {"Site" ,"IN-HOUSE_A" ,"OFFSHORE_A" ,"OFFSHORE_B" ,"OFFSHORE_C" ,"IN-HOUSE_A" ,"IN-HOUSE_B" ,"OFFSHORE_C" ,"IN-HOUSE_C" ,"IN-HOUSE_A"}, {"AID" ,"123" ,"123" ,"123" ,"123" ,"345" ,"567" ,"567" ,"789" ,"890"}, {"Project_Name" ,"Project_Name_1" ,"Project_Name_1" ,"Project_Name_1" ,"Project_Name_1" ,"Project_Name_2" ,"Project_Name_2" ,"Project_Name_2" ,"Project_Name_3" ,"Project_Name_3"}, {"Month" ,"Jan_2016" ,"Jan_2016" ,"Jan_2016" ,"Jan_2016" ,"Jan_2016" ,"Jan_2016" ,"Jan_2016" ,"Jan_2016" ,"Jan_2016"}, {"Budget" ,"400" ,"800" ,"600" ,"700" ,"1200" ,"200" ,"1000" ,"400" ,"600"} })),
    Source = qSourceData,
    DuplicateColumn = Table.DuplicateColumn(Source, "Site", "Site - Copy"),
    LocationCatAndLocation = Table.SplitColumn(DuplicateColumn,"Site - Copy",Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true),{"LocationCat", "Location"}),
    GroupAidProjectNameMonth = Table.Group(LocationCatAndLocation, {"AID", "Project_Name", "Month"}, {{"Count", each _, type table}}),
    Classes = Table.AddColumn(GroupAidProjectNameMonth, "Class", each Text.Combine(List.Distinct([Count][LocationCat]), "_")),
    ShowClasses = Table.ExpandTableColumn(Classes, "Count", {"Site", "Budget", "LocationCat", "Location"}, {"Site", "Budget", "LocationCat", "Location"}),
    Categories = Table.AddColumn(ShowClasses, "Cat", each if [LocationCat]="IN-HOUSE" and [Class]="IN-HOUSE_OFFSHORE" then "MANAGED" else if [LocationCat]="IN-HOUSE" and [Class]="IN-HOUSE" then "CODED" else "IN-HOUSE"),
    FilterForManagedSite = Table.SelectRows(Categories, each ([LocationCat] = "IN-HOUSE")),
    ManagedLocation = Table.Group(FilterForManagedSite, {"AID", "Project_Name"}, {{"ManagedLocation", each List.Max([Location]), type text}}),
    MergeManagedLocatsion = Table.NestedJoin(Categories,{"AID", "Project_Name"},ManagedLocation,{"AID", "Project_Name"},"NewColumn",JoinKind.LeftOuter),
    ExpandMgtLoc = Table.ExpandTableColumn(MergeManagedLocatsion, "NewColumn", {"ManagedLocation"}, {"ManagedLocation"}),
    OffshoreLocation = Table.AddColumn(ExpandMgtLoc, "OffshoreLocation", each if [LocationCat]="OFFSHORE" then [Location]&"-" else ""),
    CustomColumn = Table.AddColumn(OffshoreLocation, "Custom_Column", each [LocationCat]&"_"&[OffshoreLocation]&[Cat]&"_"&[ManagedLocation]),
    ReorderCols = Table.ReorderColumns(CustomColumn,{"Site", "AID", "Project_Name", "Month", "LocationCat", "Location", "Class", "Cat", "ManagedLocation", "OffshoreLocation", "Custom_Column"})
in
    ReorderCols

If you're new to M, here's how to use the code: http://www.thebiccountant.com/2016/03/28/how-to-deal-with-m-code-samples/

Please let me know if this description works for you. Thanks a lot.

You will see that there will be created a couple of "intermediate" helper-columns that might help you for your further analysis.

5 Replies

ImkeF
Community Champion
10 years ago
This is a very thorough description of your problem, but not easy to understand without actually seeing the tables.
It would help tremenously, if you could provide a sample file with some sample input data and desired output.
- agustin_garcia
  Frequent Visitor
  10 years ago
  Thanks Imke for your reply and interest.
  This scenario is basically a single table (as shown below). Columns PROJECTNAME-MONTH) would be essentially the way to identify unique projects. SITE column would tell us whether it's INHOUSE or OFFSHORE.
  
  The challenge is that I need to find two (or more) rows with same ProjectName and Month, once "grouped" then I'd like to assign a CUSTOM_COLUMN value of OUTSOURCED_OFFSHORE_A_and_MANAGED_BY_INHOUSE_A. If ONLY one single row is found, that I want to mark it as 100%_INHOUSE_MANAGED_AT_SITE_A
  
  NOTES:
  1)The depicted values for Custom_Column are examples only, not that I'd use those long values. Hope this clarifies my objective.
  2) I think the attached table (Excel), depicts all the valid scenarios.
  
  Thanks again for looking into it.
  - ImkeF
    Community Champion
    10 years ago
    Thank you, thats better - but I'm still having problems to understand the request.
    Assumption to clarify:
    1) The table you've provided is input and output at the same time. So if you're speaking about 'grouping" you actually mean an imaginary grouping. If not, please provide a table with the details that match the desired output.
    
    I can see some of the logic you've described in Project 1: Because this project has OFFSHORE-lines as well as INHOUSE-lines we add an INHOUSE-criteria in the new column to indicate that there is some internal involvment.
    
    But now comes Project 2 where we have 2 INHOUSE-involvment (A and B) and one OFFSHORE.
    1) Why does the OFFSHORE-line doesn't include any 'OFFSHORE" in the Custom-Column?
    2) What determines which of the INHOUSE-lines (A or B) is actually allocated there?
    
    No mention of the logic behind the CODED-code so far.
    
    It looks that you have no idea of how much time for you and me would be saved if you would just provide 2 simple tables as a start for this problem solving:
    1) input
    2) desired output
    with a proper list of all possible (business) combinations. Please consider investing some time in providing this.

Forum Discussion

Iterate over same table to summarize (parent-child like relationship) based in duplicates

5 Replies

Related content

Parent/child hierarchy creation

Parent Child Relationship PATH and Visual

Power Query - flatten Parent-child hierarchy

Measure to give value of parent in parent/child relationship

Parent/Child Hierarchy with duplicates in Employee ID