Transforming Sharepoint metadata column

[hyamanieu]

We are working in a multicultural company.

Some of our semantic models use as sources Sharepoint lists. When checking in the sources, we set `ViewMode="All"` where metadata columns are also pulled. Except sometimes they are pulled in GErman, sometimes in English, and sometimes in French.

Unfortunately, transforming these columns is done based on the name, e.g. https://learn.microsoft.com/en-us/powerquery-m/table-removecolumns (this is only an example).

The consequence is that:

• When working locally, a data transformation can work but will not work on the published version
• When changing the cloud-side source of a data, if the "source owner" has its locals set in English, in French, or in German, a functional transformation can work in one case, but not in the other.

Question:

• Except by setting in everysingle function "error ignore" and work on all translation possibles of metadata columns, how could we set the language we want for the data transformation? Neither in SharePoint.Tables (for the first step "source), nor in `Source{[ID=...]}[Items]` (second step "Navigation") could I find help in the doc to make a specific language mandatory.
• Where is the documentation detailing these conflicting metadata column names depending on the language? I couldn't find any.
• How can I work on transformation directly on the cloud version of my semantic models? There is no access to it, whether through desktop nor app.powerbi.com, I can only publish my functional local model to the cloud and cross fingers.

One example: "Expression.Error: The column 'Color Tag' of the table wasn't found.. Color Tag. . The exception was raised by the IDbCommand interface. Table: <tablename>" ==> The transformation expects "Farbenkennzeichnung" in the cloud version. (I have replaced the real name by <tablename>)

Thank you in advance for your help.

[hyamanieu]

Thanks for your response.

There is no way to access a column using a UUID instead of a name?

[andrewsommer]

Unfortunately, SharePoint does not provide a built-in parameter in SharePoint.Tables or Power Query (M) to enforce a specific language for metadata column names. The language of system-generated metadata columns depends on the locale settings of the user who initially set up the connection.

You can create a mapping table that detects column names dynamically and then removes the column based on the recognized name.

Please mark this post as solution if it helps you. Appreciate Kudos.

[hyamanieu · Accepted solution]

Normally, the solution could have been to use ApiVersion 15 as mentionned in the doc:

ApiVersion: A number (14 or 15) or the text "Auto" that specifies the SharePoint API version to use for this site. When not specified, API version 14 is used. When Auto is specified, the server version will be automatically discovered if possible, otherwise version defaults to 14. Non-English SharePoint sites require at least version 15.

Excep passing `ApiVersion` doesn't work 🙂 : `Expression.Error: The following are not valid SharePoint list 2.0 options: ApiVersion.`

OK, so the only solution is to do a remapping as mentionned by @andrewsommer

This is highly unsatisfactory as if someone with a new locale works on the same report, it will not work until his/her language is added in the remapping and, much much worse, if someone (even a standard user) wants to change the name of a Sharepoint List column in his/her own locale, then it will break it all. I can't believe Microsoft did such a bad API using labels instead of unique names! Is there an area where we can tell Microsoft this must be changed?

Here an example of a working remapping:

let
    Source = ...,  // your source table

    // Mapping from both German and English to German (display names)
    RenameMap = {
        {"Color Tag", "Farbkennzeichnung"},
        {"ID", "ID"},
        {"Content Type", "Inhaltstyp"},
        {"Version", "Version"},
        {"Attachments", "Anlagen"},
        {"Edit", "Bearbeiten"},
        {"Type", "Typ"},
        {"Item Child Count", "Untergeordnete Elementanzahl"},
        {"Folder Child Count", "Untergeordnete Ordneranzahl"},
        {"Label setting", "Bezeichnungseinstellung"},
        {"Retention label", "Aufbewahrungsbezeichnung"},
        {"Retention label Applied", "Aufbewahrungsbezeichnung angewendet."},
        {"Label applied by", "Bezeichnung angewendet von"},
        {"Item is a Record", "Element ist eine Aufzeichnung"},
        {"App Created By", "App erstellt von"},
        {"App Modified By", "App geändert von"}
    },

    ExistingCols = Table.ColumnNames(Source),
    FilteredRenameMap = List.Select(RenameMap, each List.Contains(ExistingCols, _{0})),
    Renamed = Table.RenameColumns(Source, FilteredRenameMap)
in
    Renamed