Getting Different Versions Of Data With Value.Versions In Power Query

Something I mentioned in my recent post about the new DeltaLake.Tables M function on the Fabric blog recently was the fact that you can get different versions of the data held in a Delta table using the Value.Versions M function. In fact, the Value.Versions is the way to access different versions of data in any source that has this concept – so long as Power Query has added support for doing so. The bad news is that, at least at the the time of writing, apart from the DeltaLake connector there’s only one other source where Value.Versions can be used in this way: the connector for Fabric Lakehouses.

Here’s how you can access the data in a table using the Lakehouse.Contents M function:

let
  Source          = Lakehouse.Contents(), 
  SelectWorkspace = Source{[workspaceId = "insertworkspaceid"]}[Data], 
  SelectLakehouse = SelectWorkspace{[lakehouseId = "insertlakehouseid"]}[Data], 
  SelectTable     = SelectLakehouse{[Id = "nested_table", ItemKind = "Table"]}[Data]
in
  SelectTable

As with DeltaLake.Table, you can get a table with all the different versions available using Value.Versions:

let
  Source          = Lakehouse.Contents(),
  SelectWorkspace = Source{[workspaceId = "insertworkspaceid"]}[Data],
  SelectLakehouse = SelectWorkspace{[lakehouseId = "insertlakehouseid"]}[Data],
  SelectTable     = SelectLakehouse{[Id = "nested_table", ItemKind = "Table"]}[Data],
  ShowVersions    = Value.Versions(SelectTable)
in
  ShowVersions

Version 0 is the earliest version; the latest version of the data is the version with the highest number and this version can also be accessed from the row with the version number null. The nested values in the Data column are tables which give you the data for that particular version number. So, for example, if I wanted to get the data for version 2 I could click through on the nested value in the Data column in the row where the Version column contained the value 2. Here’s the M code for this:

let
  Source          = Lakehouse.Contents(),
  SelectWorkspace = Source{[workspaceId = "insertworkspaceid"]}[Data],
  SelectLakehouse = SelectWorkspace{[lakehouseId = "insertlakehouseid"]}[Data],
  SelectTable     = SelectLakehouse{[Id = "nested_table", ItemKind = "Table"]}[Data],
  ShowVersions    = Value.Versions(SelectTable),
  Data            = ShowVersions{2}[Data]
in
  Data

The Lakehouse connector uses the TDS Endpoint of the Lakehouse to get data by default, as in the first code snippet above, but if you use Value.Versions to get specific versions then this isn’t (as yet) possible so it will use a slower method to get data and performance may suffer.

Last of all, you can get the version number of the data you’re looking at using the Value.VersionIdentity function. If you’re looking at the latest version of the data then Value.VersionIdentity will return null:

let
  Source          = Lakehouse.Contents(),
  SelectWorkspace = Source{[workspaceId = "insertworkspaceid"]}[Data],
  SelectLakehouse = SelectWorkspace{[lakehouseId = "insertlakehouseid"]}[Data],
  SelectTable     = SelectLakehouse{[Id = "nested_table", ItemKind = "Table"]}[Data],
  GetVersion      = Value.VersionIdentity(SelectTable)
in
  GetVersion

If you are looking at version 2 of the data then Value.VersionIdentity will return 2:

let
  Source           = Lakehouse.Contents(),
  SelectWorkspace  = Source{[workspaceId = "insertworkspaceid"]}[Data],
  SelectLakehouse  = SelectWorkspace{[lakehouseId = "insertlakehouseid"]}[Data],
  SelectTable      = SelectLakehouse{[Id = "nested_table", ItemKind = "Table"]}[Data],
  ShowVersions     = Value.Versions(SelectTable),
  GetVersion2      = ShowVersions{2}[Data],
  GetVersionNumber = Value.VersionIdentity(GetVersion2)
in
  GetVersionNumber