Forum Discussion

agustin_garcia's avatar
agustin_garcia
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's avatar
    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's avatar
ImkeF
Icon for Community Champion rankCommunity 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's avatar
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's avatar
    ImkeF
    Icon for Community Champion rankCommunity 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.