Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
Here is a link to the PBIX file for your reference.
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 ;). |
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. | Proud to be a 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:
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 ;). |
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. | Proud to be a Super User! |
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
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:
Here is a link to the PBIX file for your reference.
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 ;). |
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. | Proud to be a Super User! |