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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DenisBergeron
Helper I
Helper I

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

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
DenisBergeron
Helper I
Helper I

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.

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.

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 😉 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors