cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 ;-) 

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 ;-) 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 105 members 1,400 guests
Please welcome our newest community members: