cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mattlore
New Member

Creating a relationship between two queries (Dynamics CRM 2015 Online)

Good afternoon

 

So I'm relatively new to Power Bi desktop and I'm testing it's reporting functionality against our CRM 2015 online instance and wondering if someone my point me in the right direction.

 

I have two tables that have an existing relationship in our CRM database

Accounts and Signups.

The shared, unique value between them is the contract number field. Our signup table contains a field called "Agent" which I wish to have in the report (Ex: All accounts signed up by a speciic agent). Note: Agent field does not exist on the account table.

 

So I know I can create the relationship between them using the report builder, however; we have over 50,000 records in our accounts table, so I want to filter against different data fields that we have on the account table.

 

So my question is: Is there a way to build the relationship between the two tables during the Query building and have the data update in one table, based on a filter in another.

 

Thanks

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

OK, so I proved this out and it works. What I did was start out with two CSV files:

 

one.csv

Column1,Column2
one,10
two,20
three,30
four,40
five,50
six,60
seven,70
eight,80
nine,90

 

two.csv

Column1,Column2
one,100
two,200
three,300
four,400
five,500
six,600
seven,700
eight,800
nine,900

 

The "normal" process is to create a query for both one.csv and two.csv, then go and edit one.csv query and add a Merge step to merge two.csv query. With this method, you wind up with 2 queries, the two.csv query is in effect an intermediate query. In the Advanced editor, the code looks like:

 

two.csv

let
    Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers1" = Table.PromoteHeaders(Source1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}})
in
    #"Changed Type1"

 

one.csv

let
    Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},two,{"Column1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
    #"Expanded NewColumn"

 

However, you can use the code from these queries to create a blank query that looks like this:

 

merge query

 

let
    Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
    Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Promoted Headers1" = Table.PromoteHeaders(Source1),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}})
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},#"Changed Type1",{"Column1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
    #"Expanded NewColumn"

 

Now you can get rid of one.csv and two.csv queries and have a single query that does it all in one query! 🙂

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Greg_Deckler
Super User IV
Super User IV

OK, so I proved this out and it works. What I did was start out with two CSV files:

 

one.csv

Column1,Column2
one,10
two,20
three,30
four,40
five,50
six,60
seven,70
eight,80
nine,90

 

two.csv

Column1,Column2
one,100
two,200
three,300
four,400
five,500
six,600
seven,700
eight,800
nine,900

 

The "normal" process is to create a query for both one.csv and two.csv, then go and edit one.csv query and add a Merge step to merge two.csv query. With this method, you wind up with 2 queries, the two.csv query is in effect an intermediate query. In the Advanced editor, the code looks like:

 

two.csv

let
    Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers1" = Table.PromoteHeaders(Source1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}})
in
    #"Changed Type1"

 

one.csv

let
    Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},two,{"Column1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
    #"Expanded NewColumn"

 

However, you can use the code from these queries to create a blank query that looks like this:

 

merge query

 

let
    Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
    Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Promoted Headers1" = Table.PromoteHeaders(Source1),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}})
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},#"Changed Type1",{"Column1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
    #"Expanded NewColumn"

 

Now you can get rid of one.csv and two.csv queries and have a single query that does it all in one query! 🙂

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Awesome thank you very much! Merged queries and this method have really helped me out. Thanks!

Greg_Deckler
Super User IV
Super User IV

So, you could definitely do this with a Merge query but you would create 2 queries and then create the Merge query to combine them related on a field. However, in theory you *should* be able to combine the 2 queries into a single query and not have the 2nd table. I've done some experiments along these lines but basically the process is using the Advanced Viewer to copy and paste the code of both queries to get a single query that processes both data sources as a single query and does the merge between them. I still need to do a few more experiments to prove this out fully but in theory it should be possible.

 

Have you checked out the content pack for CRM Online? Also, you may get some benefits from looking a my article here:

https://www.linkedin.com/pulse/building-ultimate-microsoft-crm-dashboard-under-hour-greg-deckler?trk...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors