Forum Discussion
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
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,90two.csv
Column1,Column2
one,100
two,200
three,300
four,400
five,500
six,600
seven,700
eight,800
nine,900The "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! :)
3 Replies
- Greg_Deckler
Community Champion
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:
- Greg_Deckler
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,90two.csv
Column1,Column2
one,100
two,200
three,300
four,400
five,500
six,600
seven,700
eight,800
nine,900The "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! :)
Awesome thank you very much! Merged queries and this method have really helped me out. Thanks!
