Forum Discussion

hyamanieu's avatar
hyamanieu
New Member
1 year ago
Solved

Transforming Sharepoint metadata column

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. ...
Dataflow
  • hyamanieu's avatar
    hyamanieu
    1 year ago

    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

     

     

andrewsommer's avatar
andrewsommer
Super User
1 year ago

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's avatar
hyamanieu
New Member
1 year ago

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