Possible Bug with Merge in M Query Editor

[JohnMar67]

Hi,

Since the latest upgrades to Desktop i think i have found an issue with the Merge Query. It no longer allows you to merge two tables if the second table is created using reference to the first. 

here is a dummy example....  

I have some Sales data

Country	Product	Sales
UK	Bread	10
UK	Milk	20
USA	Bread	50
USA	Milk	200
France	Bread	30
France	Milk	10

from this table I generate a Total Sales table and use this to create a sort column

CountryTotal SalesSort

USA	250	1
France	40	2
UK	30	3

I disable the load, and then try to merge the Total Sales and Sort back on to the main table, but it will not allow me to select the second table and it appears in the Query list with a warning  sign beside it.  This used to work, and we have used it in previous reports, the previous reports still work, btu we can not make changes to the merges in these files.

I'm assuming a mechanism has been created to try and stop Circular references in the model. can this please just be changed to a warning and the previous functionality fixed?

John

[v-qiuyu-msft]

Hi @JohnMar67,

I tested on my side, create two tables as you described: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVW0lFyKkpNTAHShgZKsTpQMd/MnGwgZQQVCnZEUmeKLAhXCBF0K0rMS05FUmyMJg5VD7IrFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, Product = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Product", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type"

let
    #"Grouped Rows" = Table.Group(Table1, {"Country"}, {{"Total", each List.Sum([Sales]), type number}})
in
    #"Grouped Rows"

Select the Table1, then use the Merge Queries feature, Table1(2) is not available to perform merge both in Power BI desktop 2.65.5313.621 64-bit (December 2018) and 2.62.5222.601 64-bit (September 2018). Please check if you does use Merge Queries in Table1, and which Power BI desktop do you mean is working. 

You can select the Table1(2) then use Merge Queries, or select the Table1 then use Merge Queries as New. 

Best Regards,
Qiuyun Yu 

[JohnMar67]

Hi Qiuyun Yu

So you agree that we can no longer join the data back to the original table, but need to do a right join on the created table? Obviously this is not ideal as the example is just a simple example and most of our datasets are much more complex.....

[v-qiuyu-msft]

Hi @JohnMar67,

Based on my test, it's not supported to merge created table to original table follow your description. 

Do you remember which Power BI desktop version supports this action as you mention " This used to work, and we have used it in previous reports, the previous reports still work, but we can not make changes to the merges in these files."

Best Regards,
Qiuyun Yu 

[JohnMar67]

I'm pretty sure this was working in the September release of the desktop software. In fact I feel it stopped working when the new M Query Assistance was added...

[v-qiuyu-msft]

Hi @JohnMar67,

Do you mean 2018 September desktop version? I tested again in Power BI desktop 2.62.5222.601 64-bit (September 2018), merge queries doesn't work follow your description, you can see: 

Best Regards,
Qiuyun Yu