cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Power Query (M) Select * from A where A.id = B.ID and B.date is last

Hello everybody.

I'm quite new ot PowerBI.

I try and fail doing this in power Query on Power BI.

I try and fail to find a exemple of this.

 

I have a BD Snapshot (that have all the snapshot from lot BDs and Tables).

 

In the Snapshot reference table I have "date_of_snapshot" and "SnapshotID".

And all the tables have a column "SnapshotID"

 

What I want to do, is simple, take the "SnapshotID" related to the  most recent "date_of_snapshot" and get all other query of to load only the data related to this last "SnapshotID".

 

Thanks stranger for your help

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

The Final solution

For future reference :

 

let
    Source = Sql.Database("MyDataBase.local", "DataWarehouse", [HierarchicalNavigation=true, MultiSubnetFailover=true, Query="
                Declare @SnapID VARCHAR(MAX);
                Declare @Instruction VARCHAR(MAX);
                SET @SnapID = (SELECT TOP (1) SnapShotId  FROM [DataWarehouse].[dbo].[Snapshots] ORDER BY EndedOn DESC);
                SELECT * FROM [DataWarehouse].[dbo].[MyTable]  Where SnapshotId in (@SnapID)"])
in
    Source

So simple, but so research 😉 

View solution in original post

3 REPLIES 3
Highlighted
Frequent Visitor

Re: Power Query (M) Select * from A where A.id = B.ID and B.date is last

In MS-SSMS I can do this :

SELECT TOP (1) *  FROM [DataWarehouse].[dbo].[Snapshots] ORDER BY EndedOn DESC

but when I try this :

let
    Source = Sql.Database("mydatabase.local", "DataWarehouse", Query="SELECT TOP (1) *  FROM [DataWarehouse].[dbo].[Snapshots] ORDER BY EndedOn DESC"),
    dbo_Snapshots = Source{[Schema="dbo",Item="Snapshots"]}[Data]
in
    dbo_Snapshots

I got no result in PowerBI.

 

So I think I mess somewhere, but I can find it.

Highlighted
Frequent Visitor

Continuing to reply to my self

let
    Source = Sql.Database("mydatabase.local", "DataWarehouse", [Query="SELECT TOP (1) *  FROM [DataWarehouse].[dbo].[Snapshots] ORDER BY EndedOn DESC"]),
    dbo_Snapshots = Source{[Schema="dbo",Item="Snapshots"]}[Data]
in
    dbo_Snapshots

For some how, you need to write [ query ] didn't see this documented, but that how the automatic generation work.

Now, I just need to find how my other select can union with the snapshot ID.

Highlighted
Frequent Visitor

The Final solution

For future reference :

 

let
    Source = Sql.Database("MyDataBase.local", "DataWarehouse", [HierarchicalNavigation=true, MultiSubnetFailover=true, Query="
                Declare @SnapID VARCHAR(MAX);
                Declare @Instruction VARCHAR(MAX);
                SET @SnapID = (SELECT TOP (1) SnapShotId  FROM [DataWarehouse].[dbo].[Snapshots] ORDER BY EndedOn DESC);
                SELECT * FROM [DataWarehouse].[dbo].[MyTable]  Where SnapshotId in (@SnapID)"])
in
    Source

So simple, but so research 😉 

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!