Merge Queries with different IDs

Question

Hi,&nbsp;I have 2 tables that I want to merge, they both have ID's some of them they share and other they don't&nbsp;Table current month&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table last monthID&nbsp; &nbsp;Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ID&nbsp; &nbsp; Value1&nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 152&nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; 453&nbsp; &nbsp; &nbsp;30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; 504&nbsp; &nbsp; &nbsp;40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; 655&nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; 706&nbsp; &nbsp; &nbsp;60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; 80&nbsp;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:&nbsp;New tableIDA&nbsp; ValueA&nbsp; IDB&nbsp; ValueB1&nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 152&nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;3&nbsp; &nbsp; &nbsp;30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;4&nbsp; &nbsp; &nbsp;40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; 455&nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; 506&nbsp; &nbsp; &nbsp;60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; 65&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp; &nbsp; &nbsp; 70&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; 80&nbsp;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.&nbsp;

tackytechtom · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
How about this?

&nbsp;
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)
&nbsp;
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"
&nbsp;
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"
&nbsp;
Let me know, how it goes 🙂
&nbsp;
/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/

anonymous · Answer

Hi,&nbsp;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

tackytechtom · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Can you post the exact error message?&nbsp;Thank you 🙂
&nbsp;
/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/

anonymous · Answer

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 · Answer

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

Forum Discussion

Merge Queries with different IDs

5 Replies

Related content

MERGE QUERIES

merging direct query queries

Merging Queries

Merge queries causing duplicate records

Merging Multiple Queries