Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
agustin_garcia
Frequent Visitor

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

1 ACCEPTED SOLUTION

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

 

Project_lsit.png

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Project_list.jpg

 

 

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. 

 

Project_list_INPUT.jpgProject_list_OUTPUT.jpg

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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.