DirectLake error – 4 GB string store limit (need help reshaping data)

Hi jaryszek ,

Thank you for reaching out to the Microsoft Community Forum.

DirectLake cannot change StringStoresCompatibilityLevel like SSAS/AAS, so the only options are Reduce cardinality and/or string length. Move the column out of the main fact table into a lookup table. Replace the string with a surrogate key or hash. Trim unnecessary parts of the string.

Please refer below solutions for Reshaping.

1. If the column is a resource path, break it into components like subscription ID, resource group, provider and account name. Then create a lookup table for these components and reference it via a surrogate key.

Use a Dataflow Gen2 or Lakehouse SQL to split the string using SPLIT() or PARSE_PATH() logic. Generate a surrogate key like ROW_NUMBER() or hash.

2. If you don’t need the full path for reporting, replace it with a short hash like SHA256 or MD5 and keep the original in a separate table for drill-through.

3. If only the Storage Account or Resource Group matters for analysis, extract that and discard the rest.

For example Instead of storing below thing.

/subscriptions/026638b2-1cd4-4348-ac1e-39cc36269aa4/resourcegroups/cloud-shell-storage-westeurope/providers/microsoft.storage/storageaccounts/csb100320023b1d6f59-

Store the below thing.

csb100320023b1d6f59

Note: Keep fact tables, numeric measures + surrogate keys. Move high-cardinality text columns to dimension tables. Avoid storing full URLs or paths in the main table.

Please refer below two alternative options to Reshape.

1. Please try below M code to Split Resource Path into Components.

let

Source = TableName,

AddSplitColumns = Table.SplitColumn(
Source,
"ResourcePath",
Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv),
{"Part1", "Part2", "Part3", "Part4", "Part5", "Part6", "Part7", "Part8", "Part9"}
),

RenameColumns = Table.RenameColumns(AddSplitColumns, {
{"Part1", "DatePrefix"},
{"Part2", "Subscriptions"},
{"Part3", "SubscriptionID"},
{"Part4", "ResourceGroups"},
{"Part5", "ResourceGroupName"},
{"Part6", "Providers"},
{"Part7", "ProviderName"},
{"Part8", "StorageAccounts"},
{"Part9", "StorageAccountName"}
}),

RemoveExtraColumns = Table.RemoveColumns(RenameColumns, {"DatePrefix", "Subscriptions", "ResourceGroups", "Providers", "StorageAccounts"}),

AddResourceKey = Table.AddIndexColumn(RemoveExtraColumns, "ResourceKey", 1, 1, Int64.Type)
in
AddResourceKey

Note: It Splits the path into parts. Renames key parts: SubscriptionID, ResourceGroupName, ProviderName, StorageAccountName. Adds a ResourceKey (surrogate key) for building relationships. You can then create a separate lookup table with these columns and keep only ResourceKey in your fact table.

In Power BI Dataflow Gen2 or Power BI Desktop, go to Transform Data. Paste the M code in Advanced Editor. Adjust TableName and column names. Load the lookup table separately and relate it to your fact table using ResourceKey.

2. SQL script for Lakehouse that splits your resource path into components and creates a lookup table with a surrogate key.

CREATE TABLE ResourceLookup AS
SELECT
ROW_NUMBER() OVER (ORDER BY ResourcePath) AS ResourceKey,
ResourcePath,
PARSENAME(REPLACE(ResourcePath, '/', '.'), 7) AS SubscriptionID,
PARSENAME(REPLACE(ResourcePath, '/', '.'), 5) AS ResourceGroupName,
PARSENAME(REPLACE(ResourcePath, '/', '.'), 3) AS ProviderName,
PARSENAME(REPLACE(ResourcePath, '/', '.'), 1) AS StorageAccountName
FROM (
SELECT DISTINCT ResourcePath FROM FactUsage
) AS DistinctPaths;

CREATE TABLE FactUsageReshaped AS
SELECT
f.Date,
rl.ResourceKey,
f.Metric
FROM FactUsage f
JOIN ResourceLookup rl
ON f.ResourcePath = rl.ResourcePath;

Note: ROW_NUMBER() generates a surrogate key for each unique resource path. PARSENAME(REPLACE(...)) converts / to . and then extracts parts from the end. Adjust indexes based on your path structure. ResourceLookup becomes your dimension table. FactUsageReshaped replaces the long string with ResourceKey.

Fact table now stores only integers (ResourceKey), reducing string store size. Lookup table holds descriptive attributes for drill-through or filtering.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh