Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.