Forum Discussion

Frequent Visitor

10 years ago

Solved

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...

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.

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.
- agustin_garcia
  Frequent Visitor
  10 years ago
  Thanks again for replying,
  For 1)Yes, it's an imaginary grouping and yes it's the same input-output table
  
  For 2.1) It was my bad, the "OFFSHORE_C-Project_Name_2-Jan_2016" record should show a value of "OFFSHORE_C-IN-HOUSE_B".
  Records 5 and 6-7, are technically the same Project; however, record 5 is 100% Coded and Manged in-house (Site A), while record 6-7 Coding is outsourced (offshore site C) and Managed by Site B.
  
  For 2.2) Sorry again, I didn't catch that one in the table that I shared. There is actually another column (AID) that helps to differentiate between Projects at record 5 (with AID: 345) and records 6-7 (with AID: 567). See image below.
  
  For 3)
  I think the combinations depicted in the table are all combinations. I'm providing an input and output Table samples that I could think of. I thought on adding an "OUTSOURCED: Yes/No" and IN_HOUSE_BUDGET and OFFSHORE_BUDGET columns, but since a Project could be outsourced to several Offshore sites at the same time, that would imply to split the output into two tables (In-house Table and Outsourced Table) and since I'm DAX newbie, I found it difficult to think on those terms.
  
  NOTE: It may not make any difference on the DAX Code, but just in case let me explain that for Budgeting purposes at the Site level these two projects are different, however being the same Project name at the Org level they are the same (hence why I need to use the same project name)
  
  Thanks again
  - ImkeF
    Community Champion
    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.

Forum Discussion

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

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