Forum Discussion
Steps for Creating Multiple Step Joins on Different Keys
Somehow this request feels a bit strange, but anyway - here we go:
// Table2 let Source = YourTable2, #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Location", type text}, {"Category 1", type text}, {"Category 2", type text}, {"Category 3", type text}, {"Category 4", type text}}) in #"Changed Type" // Table1 let
Source = YourTable1,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Category 1", type text}, {"Category 2", type text}, {"Category 3", type text}, {"Category 4", type text}, {"Hours", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
MatchCat4 = Table.NestedJoin(#"Added Index",{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},DistinctCats,{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},"Cat4",JoinKind.LeftOuter),
StrikesCat4 = Table.AggregateTableColumn(MatchCat4, "Cat4", {{"First", List.Last, "Cat4"}}),
MatchCat3 = Table.NestedJoin(StrikesCat4,{"Location", "Category 1", "Category 2", "Category 3"},DistinctCats,{"Location", "Category 1", "Category 2", "Category 3"},"NewColumn",JoinKind.LeftOuter),
StrikesCat3 = Table.AggregateTableColumn(MatchCat3, "NewColumn", {{"First", List.Last, "Cat3"}}),
MatchCat2 = Table.NestedJoin(StrikesCat3,{"Location", "Category 1", "Category 2"},DistinctCats,{"Location", "Category 1", "Category 2"},"NewColumn",JoinKind.LeftOuter),
StrikesCat2 = Table.AggregateTableColumn(MatchCat2, "NewColumn", {{"First", List.Last, "Cat2"}}),
HighestMatchLevel = Table.AddColumn(StrikesCat2, "HighestMatchLevel", each List.Min({[Cat4],[Cat3],[Cat2]}))
in
HighestMatchLevel // DistinctCats let Source = Table2, #"Grouped Rows" = Table.Group(Source, {"Location", "Category 1", "Category 2", "Category 3", "Category 4"}, {{"First", each List.Min([ID]), type number}, {"CountIDs", each Table.RowCount(_), type number}}) in #"Grouped Rows" // Result let Source = Table2, MergeDistinctCats = Table.NestedJoin(Source,{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},DistinctCats,{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},"NewColumn",JoinKind.LeftOuter), ShowLookups = Table.ExpandTableColumn(MergeDistinctCats, "NewColumn", {"First", "CountIDs", "AllIDs"}, {"First", "CountIDs", "AllIDs"}), MergeTable1OnID = Table.NestedJoin(ShowLookups,{"First"},Table1,{"HighestMatchLevel"},"NewColumn",JoinKind.LeftOuter), ShowTotalHours = Table.ExpandTableColumn(MergeTable1OnID, "NewColumn", {"Hours"}, {"TotalHours"}), CalculateHoursPerID = Table.AddColumn(ShowTotalHours, "HoursPerID", each [TotalHours]/[CountIDs]) in CalculateHoursPerIDThe query-names are given as //... at the beginning of each query
Somehow this request feels a bit strange, but anyway - here we go:
// Table2
let
Source = YourTable2,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Location", type text}, {"Category 1", type text}, {"Category 2", type text}, {"Category 3", type text}, {"Category 4", type text}})
in
#"Changed Type"
// Table1
let
Source = YourTable1,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Category 1", type text}, {"Category 2", type text}, {"Category 3", type text}, {"Category 4", type text}, {"Hours", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
MatchCat4 = Table.NestedJoin(#"Added Index",{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},DistinctCats,{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},"Cat4",JoinKind.LeftOuter),
StrikesCat4 = Table.AggregateTableColumn(MatchCat4, "Cat4", {{"First", List.Last, "Cat4"}}),
MatchCat3 = Table.NestedJoin(StrikesCat4,{"Location", "Category 1", "Category 2", "Category 3"},DistinctCats,{"Location", "Category 1", "Category 2", "Category 3"},"NewColumn",JoinKind.LeftOuter),
StrikesCat3 = Table.AggregateTableColumn(MatchCat3, "NewColumn", {{"First", List.Last, "Cat3"}}),
MatchCat2 = Table.NestedJoin(StrikesCat3,{"Location", "Category 1", "Category 2"},DistinctCats,{"Location", "Category 1", "Category 2"},"NewColumn",JoinKind.LeftOuter),
StrikesCat2 = Table.AggregateTableColumn(MatchCat2, "NewColumn", {{"First", List.Last, "Cat2"}}),
HighestMatchLevel = Table.AddColumn(StrikesCat2, "HighestMatchLevel", each List.Min({[Cat4],[Cat3],[Cat2]}))
in
HighestMatchLevel
// DistinctCats
let
Source = Table2,
#"Grouped Rows" = Table.Group(Source, {"Location", "Category 1", "Category 2", "Category 3", "Category 4"}, {{"First", each List.Min([ID]), type number}, {"CountIDs", each Table.RowCount(_), type number}})
in
#"Grouped Rows"
// Result
let
Source = Table2,
MergeDistinctCats = Table.NestedJoin(Source,{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},DistinctCats,{"Location", "Category 1", "Category 2", "Category 3", "Category 4"},"NewColumn",JoinKind.LeftOuter),
ShowLookups = Table.ExpandTableColumn(MergeDistinctCats, "NewColumn", {"First", "CountIDs", "AllIDs"}, {"First", "CountIDs", "AllIDs"}),
MergeTable1OnID = Table.NestedJoin(ShowLookups,{"First"},Table1,{"HighestMatchLevel"},"NewColumn",JoinKind.LeftOuter),
ShowTotalHours = Table.ExpandTableColumn(MergeTable1OnID, "NewColumn", {"Hours"}, {"TotalHours"}),
CalculateHoursPerID = Table.AddColumn(ShowTotalHours, "HoursPerID", each [TotalHours]/[CountIDs])
in
CalculateHoursPerIDThe query-names are given as //... at the beginning of each query
Hummm, okay I've gotten through the code and I think we have a bit of a misunderstanding, let me illustrate with my original tables:
Table1 (i've added an Index as you did in your code):
Location | Category 1 | Category 2 | Category 3 | Category 4 | Hours |Index WAC | Plane |Civil | Recon | Air | 1000 |1 WAC | Plane | Military | Patrol | Land | 1000 |2 WAC | Plane | Civil |Survey |Water | 1000 |3
Table2 from my original post:
ID#| Location | Category 1 | Category 2 | Category 3 | Category 4 001| WAC | Plane | Civil | Recon | Air 002| WAC | Plane | Civil | Recon | Air 003| WAC | Plane | Military | Patrol | 004| WAC | Plane | Civil | |
When I do the Matching on my most detialed level ID# 001 AND 002 both match with Index #1.
Accordingly in my final output table (Index added):
Location | Category 1 | Category 2 | Category 3 | Category 4 | Hours | Index| CountMatchingTable2.ID#| Table2.ID# WAC | Plane | Civil | Recon | Air | 1000 | 1 | 2 | 001 (match on Loc & C1-4) WAC | Plane | Civil | Recon | Air | 1000 | 1 | 2 | 002 (match on Loc & C1-4) WAC | Plane | Military | Patrol | Land | 1000 | 2 | 1 | 003 (match on Loc & C1-3) WAC | Plane | Civil |Survey |Water | 1000 | 3 | 1 | 004 (match on Loc & C1-2)
Index 1 has duplicated and 1000 will be divided by 2 matching ID's splitting 500 hours to ID#001 and ID#002. I believe by using "List.Last" in your "Strikes" this sort of duplication will not be possible.....
I hear you on this feeling odd, but really all that I am trying to accomplish is making sure that my Hours make it back to the ID#'s at the most detailed levels possible and then not have the hours ever double-counted. I'm open to a different approach if you can think of one...
Hi Mark,
please have a look at my code again. It should do exactly what you've requested (at least at this point): The result-query starts with query 2 (which is in your example the table with the ID's and incomplete Category-allocations).
So please check your table names and the according references in your code.
Hmmm... no it's basically not allowing ID# 002 to match back with Table1 only ID#001....
Could it be the List.Min in the DistinctCats query? Wouldn't that always just take ID#001 rather than both ID 001 and 002 when the count of IDs is > 1?
This is the result I got:
Please check if this is your desired result.
If that’s your desired result, please post your query code so that I can have a look to check.