0 Kudos

Possible Bug with Merge in M Query Editor

Status: New
by JohnMar67 Visitor on ‎12-12-2018 12:33 AM

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

 

CountryProductSales
UKBread10
UKMilk20
USABread50
USAMilk200
FranceBread30
FranceMilk10

 

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

 

CountryTotal SalesSort

USA2501
France402
UK303

 

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

Comments
by Moderator v-qiuyu-msft
on ‎12-12-2018 06:44 PM

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"

q4.PNG

 

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

q5.PNG

 

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 

by JohnMar67 Visitor
on ‎12-13-2018 02:32 AM

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.....

 

by Moderator v-qiuyu-msft
on ‎12-16-2018 11:23 PM

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 

by JohnMar67 Visitor
on ‎12-19-2018 12:51 AM

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...

by Moderator v-qiuyu-msft
on ‎12-19-2018 11:24 PM

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