CODEWITHSUNDEEP
exceldynamics-crmpowerquery
tsdn
tsdn

Reputation: 427

Excel Power Query - Dynamics 365 Online - get names from another table without Merge

I am pulling some Opportunities from our Dyn365 instance into Excel using the Get Data From Dynamics 365 (online) feature. Some columns (i.e. Account, Seller, etc.) show GUIDs rather than names and in order to see names I need to use Merge Queries and pull the relevant tables into Excel.

enter image description here

Problem is, the Accounts table has ~800k records and the Sellers table isn't small either so even if I reduce the number of columns to load, it still takes about 7 minutes to refresh this query. My questions:

  1. Can this be achieved without the need for merging tables?
  2. Or, can I use merge, but not have to load Accounts and Sellers into worksheet?
  3. Is there a better way to do what i'm trying to do? (except for the Dynamic Worksheet Export)?

//Edited 31-May-2022

M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Opportunities"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OpportunityID", type text}, {"OpportunityNumber", type text}, {"Account", type text}, {"Seller", type text}}),
    #"Merged Queries" = Table.Buffer(Table.NestedJoin(#"Changed Type", {"Account"}, Accounts, {"AccountID"}, "Accounts", JoinKind.LeftOuter)),
    #"Expanded Accounts" = Table.ExpandTableColumn(#"Merged Queries", "Accounts", {"CustomerName", "Country"}, {"Accounts.CustomerName", "Accounts.Country"}),
    #"Merged Queries1" = Table.Buffer(Table.NestedJoin(#"Expanded Accounts", {"Seller"}, Sellers, {"SellerID"}, "Sellers", JoinKind.LeftOuter)),
    #"Expanded Sellers" = Table.ExpandTableColumn(#"Merged Queries1", "Sellers", {"SellerName"}, {"Sellers.SellerName"})
in
    #"Expanded Sellers"

//Edit2 - the below doesn't even want to load into preview (i.e. marching ants forever). Without the "Table.Buffer()" the preview loads within seconds.

let
    Source = OData.Feed("https://mydomain.crm.dynamics.com/api/data/v8.2/", null, [Implementation="2.0"]),
    #"BufferedOpportunities" = Table.Buffer(Source{[Name="opportunities",Signature="table"]}[Data])
in
    #"BufferedOpportunities"

Upvotes: 0

Views: 114

Answers (1)

davidebacci
davidebacci

Reputation: 30219

Regarding point 2:

  1. You can merge without loading the Accounts and Sellers tables into a worksheet. You can right click the query in the Queries & Connections pane in Excel and ensure you tick Only Create Connection for each of those two queries. The tables will then be unloaded from the workbook which will save a lot of time.

enter image description here

Edit:

Try this. I have typed it freehand so can't guarantee no mistakes but it should give you an idea of what you need to do.

let
    Source = Excel.CurrentWorkbook(){[Name="Opportunities"]}[Content],
    #"Changed Type" = Table.Buffer( Table.TransformColumnTypes(Source,{{"OpportunityID", type text}, {"OpportunityNumber", type text}, {"Account", type text}, {"Seller", type text}})),
    BufferedAccounts = Table.Buffer(Accounts),
    BufferedSellers = Table.Buffer(Sellers),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Account"}, BufferedAccounts, {"AccountID"}, "Accounts", JoinKind.LeftOuter),
    #"Expanded Accounts" = Table.ExpandTableColumn(#"Merged Queries", "Accounts", {"CustomerName", "Country"}, {"Accounts.CustomerName", "Accounts.Country"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Accounts", {"Seller"}, BufferedSellers, {"SellerID"}, "Sellers", JoinKind.LeftOuter),
    #"Expanded Sellers" = Table.ExpandTableColumn(#"Merged Queries1", "Sellers", {"SellerName"}, {"Sellers.SellerName"})
in
    #"Expanded Sellers"

Upvotes: 1

Related Questions