Direct Query Merge issue

[Anonymous]

I'm doing a simple Merge of 2 tables within the same database and Imhaving this error.

This is my Power M Code

let
    Source = Sql.Databases("xxxxx"),
    ACHV05 = Source{[Name="V05"]}[Data],
    dbo_Market = ACHV05{[Schema="dbo",Item="Market"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo_Market, "P. Market", each Number.ToText([id]) & " - " & [name]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"P. Market", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"id"}, RoutingNumber, {"marketId"}, "RoutingNumber", JoinKind.LeftOuter),
    #"Expanded RoutingNumber" = Table.ExpandTableColumn(#"Merged Queries", "RoutingNumber", {"number"}, {"RoutingNumber.number"})
in
    #"Expanded RoutingNumber"

Help, I know I can load the table and make a relationship, and problem solved, but why is this merge function giving me an error?

And also, working in the direct query, what is best? to do this kind of merges in the query (Power Query) or load all tables nad make relationships?

[lbendlin]

Cut the power query in two, then ingest both data sources separately and let the data model do the work.

[lbendlin]

agree with @parry2k  Not only is merge extremely expensive, it also breaks query folding - something that is pretty vital to a direct query connection.

Keep in mind that EVERY SINGLE user interaction in your report will fire up new direct queries against your sources.  That's already expensive without the merge.

Let the data model do the work for you.

[Anonymous]

Understood, I agree, but what if I HAVE to do it, what is that error?

[parry2k]

@Anonymous merge is very expensive, it is recommended to use relationship or if you really need merge, better to have this in the view in the backend and bring the columns you need from merge.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡