Is possible to convert a legacy partition to an M partition (new)?

[ctrip77]

Hi, it is possible to change the datasource of a table that is legacy to a new style datasource (M - powerquery)? thanks a lot.

[otykier]

Not directly, but you can use the following C# script for that:

foreach(var partition in Selected.Partitions.ToList())
{
    if(partition.SourceType == PartitionSourceType.Query)
    {
        // Add a new M partition to the table:
        var mPartition = partition.Table.AddMPartition();

        // Remove the original partition from the table:
        partition.Delete();

        // Assign the name of the partition from the name of the original partition:
        mPartition.Name = partition.Name;

        // Assign the Power Query (M) expression using the name of the original data source and the native query of the original partition:
        mPartition.Expression = "let \n" + 
            "    dataSource = #\"" + partition.DataSource.Name + "\", \n" +
            "    data = Value.NativeQuery(dataSource, \"" + partition.Query + "\") \n" +
            "in \n" +
            "    data";
    }
}

Create a new C# script in Tabular Editor, paste the above script, select the partitions you wish to convert and hit F5 to execute the script.

Hope this helps.

[ctrip77]

Thanks for the reply! I see that you change the partition to Power Query M, but the datasource is also legacy, instead of use partition.Datasource.Name, Could I change it to reference the new datasource created ? thanks!

[otykier]

@ctrip77 sorry for the late reply.

An M partition does not have any property that specifies the data source. Instead, the M expression itself references the data source by name. If you reference a legacy data source in an M expression, the Analysis Services engine internally wraps the legacy data source in a temporary OLE DB structured data source, so M partitions can reference both legacy and structured data sources without issues.