Forum Discussion
Iterate over same table to summarize (parent-child like relationship) based in duplicates
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 ReorderColsIf 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.
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
Community ChampionYou 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.