cosmos v1 to v2

[julio_gago_alon]

Hi!

I have a bunch of reports & semantic models in powerbi.com that were generated from desktop using cosmos db v1 connector, that is, the advanced editor shows "DocumentDB.Contents.(...)" in the very first step.

I need to move to v2 connector which means using CosmosDB.Contents(). I managed to do that by replacing the initial commands in the advanced editor to the ones inserted by the application when I connect to a new v2 from scratch.

I can make both compatible with a few edits, with that I mean that I can migrate a report based on v1 to v2 with a few edits.

However, my issue is currently this: v2 connector will automatically expand JSON documents, which is ok, but it will prefix the columns with the name of the field being expanded, which is a bummer, because then I have to manually replace all the names of the previous expansion, one by one, and also in the visuals.

That is a no-go.

So I am wondering if there is any method to disable that prefixing of column names during the expansion, so that I can end up having the same column names as before, where I selected the option to NO do any prefixing.

Thanks,

Julio.

[julio_gago_alon]

Awesome set of solutions! Thanks

[Anonymous · Accepted solution]

Hi @julio_gago_alon ,
Your problem is that the V2 connector prefixes column names when extending JSON, right?

Here is the solution I found for you hope it helps!
1. Custom Transformation in the Query Editor:
You can use the “Rename Columns” function to map the new prefixed column names back to the original ones.
his can be done manually for a small number of columns or programmatically using M code for large datasets.
Example M code snippet for renaming columns:

Table.RenameColumns(
Source,
{
{"prefixed_column1", "original_column1"},
{"prefixed_column2", "original_column2"}
}
)

2. Preventing Prefixing During Expansion:
Unfortunately, in Power BI, the V2 Cosmos DB connector does not provide a direct option to disable the prefixing behavior.
A possible workaround is to modify the M query after expansion:
Use Table.TransformColumnNames to remove prefixes.
Example:

Table.TransformColumnNames(Source, each Text.Replace(_, "prefix_", ""))

3. Automation for Large Models:
• If the renaming needs to be applied across multiple queries or tables:
• Create a parameterized M script to dynamically detect and remove prefixes from column names.
• Example:

let
RemovePrefixes = (table as table, prefix as text) =>
Table.TransformColumnNames(table, each Text.Remove(_, prefix))
in
RemovePrefixes

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.