Forum Discussion

Max's avatar
Max
Advocate I
10 years ago

SQL to DAX help request - Project Online data

I need help with querying data and I must confess being humbled by my DAX (or it is M?) current ignorance.

 

I have Projects in Project Online. I want to track some of them in a dashboard in Power BI.
A Custom Enterprise field called "TrackInPowerBI" was created, and it was set to "true" for the projects to be tracked to.

 

Connecting with oData was easy.

 

Selecting just those projects was easy:

 

let
Source = OData.Feed("https://mycustomer.sharepoint.com/sites/pwa/_api/ProjectData"),
Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
#"Select Columns" = Table.SelectColumns(Projects_table,{"ProjectId", "ProjectActualCost", "ProjectActualDuration"...
... "TotalBenefits", "TotalCost", "TrackInPowerBI","Country", "State"}),
.... more applied steps here...
#"Just Projects Tracked" = Table.SelectRows(#"Renamed Columns2", each ([TrackInPowerBI] = true))
in
#"Just Projects Tracked"

 

Works as a charm.

 

But doing this for a related table is not being easy for me.
Basically, what I need is the translation of

 

Select Tasks.*
from Tasks
where Tasks.ProjectId in (select ProjectId from Projects where Projects.TrackInPowerBI = 1)

 

or similar

 

Select Tasks.*
from Tasks join Projects
on Tasks.ProjectId = Projects.ProjectId
where Projects.TrackInPowerBI = 1

 

May somebody help me with this, pretty please?

The main issue here is that there are big tables like the TaskTimePhasedDataset that time-out when querying from PowerBI Desktop and I need to cut out all of those rows not belonging to the tracked projects.

 

Max

 

Need Help

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Here is a walk-through of creating a single Join query in M:

     

    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!