Forum Discussion
Update custom partition
Hi Peter_23,
Thank you for your follow up query. I apologize for any inconvenience you may have experienced.
As per the details provided previously, I have gone through the code again and made some small changed based on the requirement, so I request you to please try the below provided code if the issue persist, please feel free to reach out to us.
Code1:$updatedPartitionDefinition = @{
"createOrReplace" = @{
"object" = @{
"database" = "base"
"table" = "mytable"
"partition" = "BB"
}
"partition" = @{
"name" = "BB"
"source" = @{
"type" = "m"
"expression" = @(
"let",
"source = ...",
"#\Filtered Rows\"= Table.SelectRows(source, each [#\"Query-filter\"] = \"BB\")",
"in",
"FilteredRows"
)
}
}
}
}
Code2:
$refreshRequest = @{
"refresh" = @{
"type" = "dataOnly"
"objects" = @(
@{
"database" = "base"
"table" = "mytable"
"partition" = "BB"
}
)
}
}
If you have any further queries please feel free to reach out to us we will guide you accordingly. If this post helps, then please give us 'Kudos' and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi Peter_23,
Thank you for reaching out to the Microsoft Fabric community Forum.
Tabular model scripting language does not directly support parameterization like power bi desktop. However, you can configure and manage custom parameters for partitions in SSMS or Visual Studio and reference them in TMSL scripts. Please go through the below following steps may resolve your issue.
- Open sql server management studio connect to your analysis services instance. Navigate to the database and model in question. Verify that the custom partition parameter is correctly set up in the model’s settings. Ensure that the parameter is listed in the Parameters section and has the correct value and type text parameter in this case.
- In SSMS, navigate to the Partitions section under the relevant model. Verify that the partition settings are correct and that the custom parameter is used in the query definition.
If you need to manually update the partition, follow these steps:
- Right-click on the partition you want to update, Select Edit. Ensure that the key parameter is correctly specified in the query or filter section. Save and process the changes to see if the update is successful.
- Open Visual Studio and connect to your Analysis Services project. Navigate to the model and partitions. Verify the custom parameter setup and ensure it's correctly used in the partition definition. If needed, manually update the parameter and process the partition to reflect the changes.
- After making the necessary changes in SSMS or Visual Studio, test the model to ensure that the custom partition updates correctly. Check if the data is processed as expected with the updated parameter.
Please go through the below documentation links for better understanding.
Tabular Model Scripting Language (TMSL) Reference | Microsoft Learn
Create an Analysis Services Project | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Thanks Poojara_D12 v-kpoloju-msft
I created a script with powershell, so
"createOrReplace":{
"object":{
"database":"base"
"table":"mytable"
"partition": "AA"
},
"partition":{
"name":"AA",
"source":{
"type": "m",
"expression":[
"let",
"source..."
"#\Filtered Rows\"= Table.SelectRows (source, each [#\"Query-filter"] = KEYPARAMETER )",
in...
So with it, it's created succesfull the partition, but I updated, the partition with the next script:
"refresh":{
"type": "dataOnly",
"objects": [
{
"database": "base",
"table":"mytable",
"partition":"AA"
}
I don't find a command to pass KEYPARAMETER in this update proccess, it is possible?e.g. Overrride KEYPARAMETER default value.
thanks in advance
Hi Peter_23,
Thank you for reaching out to the Microsoft Fabric community Forum.
As per the details provided, I have gone through the code and made some small changed some based on the requirement, so I request you to please try the below provided code if the issue persist, please feel free to reach out to us.
Code1:
$updatedPartitionDefinition = @{
"createOrReplace" = @{
"object" = @{
"database" = "base"
"table" = "mytable"
"partition" = "AA"
}
"partition" = @{
"name" = "AA"
"source" = @{
"type" = "m"
"expression" = @(
"let",
"source...",
"#\Filtered Rows\"= Table.SelectRows(source, each [#\"Query-filter\"] = \"NEW_KEYPARAMETER_VALUE\")",
"in..."
)
}
}
}
}Code2:
$refreshRequest = @{
"refresh" = @{
"type" = "dataOnly"
"objects" = @(
@{
"database" = "base"
"table" = "mytable"
"partition" = "AA"
}
)
}
}I hope my suggestions give you good ideas, if you have any more questions, please feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly
Thank you.I created a new partition with your scripts:
$updatedPartitionDefinition = @{ "createOrReplace" = @{ "object" = @{ "database" = "base" "table" = "mytable" "partition" = "BB" } "partition" = @{ "name" = "BB" "source" = @{ "type" = "m" "expression" = @( "let", "source...", "#\Filtered Rows\"= Table.SelectRows(source, each [#\"Query-filter\"] = \"BB\")", "in..." ) } } } }I update the partitions with:
$refreshRequest = @{ "refresh" = @{ "type" = "dataOnly" "objects" = @( @{ "database" = "base" "table" = "mytable" "partition" = "BB" } ) } }I run the script, it update with 0 rows 😞 but the dataset it contains values with "BB" (value requiered for partition)
I don't know whats happening ..
I find xmla parameter is possible update the parameter with it?
thanks in advance.
Hi Peter_23,
Thank you for your follow up query. I apologize for any inconvenience you may have experienced.
As per the details provided previously, I have gone through the code again and made some small changed based on the requirement, so I request you to please try the below provided code if the issue persist, please feel free to reach out to us.
Code1:$updatedPartitionDefinition = @{
"createOrReplace" = @{
"object" = @{
"database" = "base"
"table" = "mytable"
"partition" = "BB"
}
"partition" = @{
"name" = "BB"
"source" = @{
"type" = "m"
"expression" = @(
"let",
"source = ...",
"#\Filtered Rows\"= Table.SelectRows(source, each [#\"Query-filter\"] = \"BB\")",
"in",
"FilteredRows"
)
}
}
}
}
Code2:
$refreshRequest = @{
"refresh" = @{
"type" = "dataOnly"
"objects" = @(
@{
"database" = "base"
"table" = "mytable"
"partition" = "BB"
}
)
}
}
If you have any further queries please feel free to reach out to us we will guide you accordingly. If this post helps, then please give us 'Kudos' and consider Accept it as a solution to help the other members find it more quickly.
Thank you.