Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

3 REPLIES 3
KNP
Super User
Super User

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:

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Anonymous
Not applicable

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 

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors