Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Benefits / Disadvantages to multiple sources in same M Query

I've read up on the M language today and watched a bunch of seminar video's to find out that  I can combine and transform multiple tables in a single M query, where i previously thought it was processed line by line.

 

For example:

let
    Source1 = SalgImport,
    #"Filter Rows" = Table.SelectRows(Source1, each [Navn] = "Jens"),
    
    Source2 = ByImport,
    #"Filter Rows2" = Table.SelectRows(Source2, each ([By] = "Rønde")),

    #"Merged Queries" = Table.NestedJoin(#"Filter Rows",{"PersonID"},#"Filter Rows2",{"PersonID"},"Merged",JoinKind.LeftOuter)
in
    #"Merged Queries"

i.e. taking two different sources, filtering them independently and then merging them.

 

One major advantage I see to this, is limiting the amount of tables in the query pane that can easily get large when working with large datasets that are always evolving.

A disadvantage is that the Applied steps pane becomes a little more confusing, but other than that is there any major disadvantages I'm missing to this approach?

 

 

For example, breaking query folding or not applying best practice?

 

I can see a good purpose to this apporach in certain scenarios, as I'm often transforming & filtering down to a single column, to then merge it up against a larger table, and after that I have no interest in my single column table and it's just taking up space and making the query pane more confusing.

General Comment
Need Help

1 Reply

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi Anonymous,

     

    Yes, your understanding about merge queries are right. By merging queries, it can simplify the query pane. Also since all the queries are combined into one single query. It can simplify the steps you handle with the data.

     

    Then the disadvantages are also right. Each steps in Power Query will be shown at Applied steps pane. The more complicated your M Query is, the more complicated the Applied steps pane will be.

     

    And if you want to simplify the steps in M Query, you can try to create a custom function for the merging queries and call this function in your M Query. However, you should know that custom function can only be used in Power BI desktop. It cannot be used in Power BI Service.

     

    Thanks,
    Xi Jin.