Merge Queries

[Anonymous]

Hi every one, 

I have an issue in merging of 2 tables as below,

THE BELOW IS TABEL 1

Project name	task
1	A,B,C
2	B,D,F
3	D,C,F
4	A,F,D

THE BELOW IS TABEL 2 

Project name	task
1	A,Z,R
2	B,U,K
3	D,H,K
4	A,B,K

each tabel contains many columns as well 

each project has 3 tasks which may be similar with the another proejct or not. 
now I need to merge them but once I do that command by selecting the Project name Columns as matching items, I can see the tasks are duplecating and makeing too many tasks for each projects. I have used Left Outer and Full outer, but same result. 

for example, project 1 will have task K that it is wrong, I need to have the same tasks for each proejcts not mixed them. 

can anyone help me how I can mereg 2 tables? 

thanks 

[KNP]

Hi @Anonymous ,

I don't know why you are having this problem.

I have recreated the example tables you have provided and the merge works as I would expect.

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUcdJxVorViVYyAvKcdFx03MA8YyDPRccZyjMBq3TTcVGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project name" = _t, task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project name", Int64.Type}, {"task", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project name"}, Table2, {"Project name"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Project name", "task"}, {"Table2.Project name", "Table2.task"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Table2", "MergedTask", each Text.Combine({[task], [Table2.task]}, ","), type text)
in
    #"Inserted Merged Column"

 Output:

I merged with a left join and expanded table 2 and then merged the task columns.

(obviously, I haven't done anything about removing the duplicate tasks for each project)

If this doesn't solve your issue, are you able to post an example of the results you get (be sure to hide any sensitive data).

Regards,

Kim

[Anonymous]

Thanks Kym, 

could you please get the tasks sepereted like the below:

porject	task
1	A
1	B
1	c
2	B
2	D

AND SO ON

could please tell me whther you have any probelm or not ?

thanks 

[KNP · Accepted solution]

I have added a few extra steps to the original code I posted to achieve this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUcdJxVorViVYyAvKcdFx03MA8YyDPRccZyjMBq3TTcVGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project name" = _t, task = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project name", Int64.Type}, {"task", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project name"}, Table2, {"Project name"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Project name", "task"}, {"Table2.Project name", "Table2.task"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Table2", "MergedTask", each Text.Combine({[task], [Table2.task]}, ","), type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Merged Column", "MergedTask", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"MergedTask.1", "MergedTask.2", "MergedTask.3", "MergedTask.4", "MergedTask.5", "MergedTask.6"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Project name", "task", "Table2.Project name", "Table2.task"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Project name", "Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

The steps that were added are:

1. Split column by delimiter
2. Unpivot other columns
3. Removed other columns
4. Removed duplicates

Here is a link to the PBIX file for your reference.

PBIX File 

Regards,

Kim