Merged Query Question

[Alfinthehouse]

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	10
Project 1	B	10
Project 1	C	10
Project 2	A	10
Project 2	B	10
Project 2	C	10
Project 3	A	10
Project 3	B	10
Project 3	C	10

Table 2:

Project	Budget
Project 1	100
Project 2	200
Project 3	300

The result I'm getting when I merge, as expected, is:

Project	Item	Cost	Budget
Project 1	A	10	100
Project 1	B	10	100
Project 1	C	10	100
Project 2	A	10	200
Project 2	B	10	200
Project 2	C	10	200
Project 3	A	10	300
Project 3	B	10	300
Project 3	C	10	300

But what I'm looking for is this:

Project	Item	Cost	Budget
Project 1	A	10	100
Project 1	B	10
Project 1	C	10
Project 2	A	10	200
Project 2	B	10
Project 2	C	10
Project 3	A	10	300
Project 3	B	10
Project 3	C	10

I need to have it all in 1 table. Is there any way to do this in PQ?

Many thanks

[ronrsnfld · Accepted solution]

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"

[Alfinthehouse]

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

[slorin]

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]

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

[Chewdata]

Hello,

Can you share your merge code, because a simple left-outer join should do the trick.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRcgRiQwOlWB1UYSfsws6YwkbYDTHCbogRdkOMsRtijN0QYyRDYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Item = _t, Cost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Item", type text}, {"Cost", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project"}, Table2, {"Project"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Budget"}, {"Budget"})
in
    #"Expanded Table2"

[shafiz_p]

Hi @Alfinthehouse  Assuming pattern A,B,C repeats every 3 row. To acheive the desired result, Fisrt merged both table and expand budget column. Then you need to create a index column start from 0 and create a custom column with mudulo divided by 3. Then you can create final budget column with condition, if 0 then budget else null. See the below code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Item", type text}, {"Cost", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project"}, Table2, {"Project"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Budget"}, {"Budget"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Table2", "Index", 1, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Custom", each if [Modulo] = 1 then [Budget] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Budget", "Index", "Modulo"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Budget"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Budget", Int64.Type}})
in
#"Changed Type1"


Desire output:

Hope this helps!!
If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[Alfinthehouse]

Thanks for the response Shafiz, unfortunately the real data is more complex and each project could have 1,000's of rows associated with it. And it wouldn't be a fixed number per project either