Merge Queries with different IDs

[Anonymous]

Hi,

I have 2 tables that I want to merge, they both have ID's some of them they share and other they don't

Table current month              Table last month

ID   Value                                ID    Value

1     10                                    1      15

2     20                                    4      45

3     30                                    5      50

4     40                                    6      65

5     50                                    7      70

6     60                                    8      80

When I append them with (Fuller Outer) I get the error that there are blanks in the ID of the tables and that it can't be possible becuase is the unique ID that joins them. This is what I am looking to get:

New table

IDA  ValueA  IDB  ValueB

1     10          1      15

2     20          

3     30          

4     40          4      45

5     50          5      50

6     60          6      65

                     7      70

                     8      80

So becuase of the values that don't match I am not able to merge my tables. I do need all of the values of both tables.

[tackytechtom]

Hi @Anonymous ,

Can you post the exact error message? 

Thank you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Column ‘ID’ un table ‘Table 1’ contains blank values and this is not allowed for columns on the one side of many-to-many relationship or for columns that are used as the primary key of the table

[Anonymous]

Column ‘ID’ un table ‘Table 1’ contains blank values and this is not allowed for columns on the one side of many-to-many relationship or for columns that are used as the primary key of the table

[tackytechtom]

Hi @Anonymous ,

How about this?

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

Table current month

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jca5DQAwCAPAXVxThHcZxP5rxJKvul04DP5wtgg21GRTLbbUZlsddtj7", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Table last month", {"ID"}, "Table last month", JoinKind.FullOuter),
    #"Expanded Table last month" = Table.ExpandTableColumn(#"Merged Queries", "Table last month", {"ID", "Value"}, {"Table last month.ID", "Table last month.Value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table last month",{{"ID", Order.Ascending}})
in
    #"Sorted Rows"

Table last month

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0VYrViVYyATJNIExTINPUAMw0AzLNIKLmQKY5RNQCyLQAMmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type"

Let me know, how it goes 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Hi,

As I understood you did a Fuller Outher join with the tables based on ID, this is what I did and is not working becuase of the blank rows