Forum Discussion
Alfinthehouse
1 year agoRegular Visitor
Merged Query Question
Hi I am trying to merge 2 tables, but in a way that it only matches once for each project. The tables below should explain what I'm after better Table 1: Project Item Cost Project 1 A ...
- 1 year ago
Different Approach:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Table1 = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Item", type text}, {"Cost", Int64.Type}}), Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], Table2 = Table.TransformColumnTypes(Source2,{{"Project", type text}, {"Budget", Int64.Type}}), Merge = Table.NestedJoin(Table1,"Project",Table2,"Project","Merged"), #"Expanded Merged" = Table.ExpandTableColumn(Merge, "Merged", {"Budget"}, {"Budget"}), #"Grouped Rows" = Table.Group(#"Expanded Merged", {"Project"}, { {"all", (t)=>Table.FromColumns( Table.ToColumns(Table.RemoveColumns(t,"Budget")) & {{t[Budget]{0}} & List.Repeat({null}, Table.RowCount(t)-1)}, {"Project","Item","Cost","Budget"}), type table [Project=nullable text, Item=nullable text, Cost=nullable number, Budget=nullable number]}}), #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Item", "Cost", "Budget"}) in #"Expanded all"
ronrsnfld
1 year agoSuper User
Different Approach:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Table1 = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Item", type text}, {"Cost", Int64.Type}}),
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Table2 = Table.TransformColumnTypes(Source2,{{"Project", type text}, {"Budget", Int64.Type}}),
Merge = Table.NestedJoin(Table1,"Project",Table2,"Project","Merged"),
#"Expanded Merged" = Table.ExpandTableColumn(Merge, "Merged", {"Budget"}, {"Budget"}),
#"Grouped Rows" = Table.Group(#"Expanded Merged", {"Project"}, {
{"all", (t)=>Table.FromColumns(
Table.ToColumns(Table.RemoveColumns(t,"Budget"))
& {{t[Budget]{0}} & List.Repeat({null}, Table.RowCount(t)-1)},
{"Project","Item","Cost","Budget"}),
type table [Project=nullable text, Item=nullable text, Cost=nullable number, Budget=nullable number]}}),
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Item", "Cost", "Budget"})
in
#"Expanded all"
- Alfinthehouse1 year agoRegular Visitor
Hi ronrsnfld
With a bit of fiddling around to make it match my actual data, this worked a treat. Thanks so much for this and will accept as the solution
Thanks
Allan