cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arsham Helper I
Helper I

Merge Queries

Hi every one, 

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

 

THE BELOW IS TABEL 1

Project nametask

1

A,B,C
2B,D,F
3D,C,F
4A,F,D

THE BELOW IS TABEL 2 

Project nametask

1

A,Z,R
2B,U,K
3D,H,K
4A,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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
KNP Resolver I
Resolver I

Re: Merge Queries

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

View solution in original post

3 REPLIES 3
KNP Resolver I
Resolver I

Re: Merge Queries

Hi @Arsham ,

 

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:

image

 

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

Arsham Helper I
Helper I

Re: Merge Queries

Thanks Kym, 

could you please get the tasks sepereted like the below:

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

AND SO ON

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

thanks 

KNP Resolver I
Resolver I

Re: Merge Queries

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

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors