Forum Discussion

Alfinthehouse's avatar
Alfinthehouse
Regular Visitor
2 years ago
Solved

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 ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years 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"

     

     

slorin's avatar
slorin
Icon for Super User rankSuper User
2 years ago

Hi Alfinthehouse 

 

let
Source = Table1,
Group_by_Project = Table.Group(Source, {"Project"}, {{"Data", each _, type table}}),
Join_Projet = Table.NestedJoin(Group_by_Project, {"Project"}, Table2, {"Project"}, "Table2", JoinKind.LeftOuter),
Expand_Budget = Table.ExpandTableColumn(Join_Projet, "Table2", {"Budget"}, {"Budget"}),
Data_add_Budget = Table.AddColumn(Expand_Budget, "Data2", 
     each Table.FromColumns(
       Table.ToColumns([Data]) & {{[Budget]}},
       Table.ColumnNames([Data]) & {"Budget"})),
Combine = Table.Combine(Data_add_Budget[Data2])
in
Combine

Stéphane 

  • Alfinthehouse's avatar
    Alfinthehouse
    Regular Visitor
    2 years ago

    Hi slorin 

     

    When I tried this I got a circular reference error. I could probably have figured it out, but since the other solution in the thread worked I didn't try to solve it. All the same, thanks so much for you input on this, much appreciated

     

    Thanks
    Allan