Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 

 

Need Help
  • KNP's avatar
    KNP
    6 years ago

    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

3 Replies

  • KNP's avatar
    KNP
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        KNP
        Super User
        6 years ago

        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