cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DenisBergeron Frequent Visitor
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
DenisBergeron Frequent Visitor
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
DenisBergeron Frequent Visitor
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.

DenisBergeron Frequent Visitor
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.

DenisBergeron Frequent Visitor
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 279 members 2,705 guests
Please welcome our newest community members: