UTS Integration Rejection — Excel to SharePoint List

Objective

To import into or update data in the UTS Integration Rejection SharePoint list from UTS_INTEGRATION_REJECTION_aktuell.xlsx and UTS_RELEASES_aktuell.xlsb based on certain conditions.

Requirements from the requestor

Introduction

Each row in the SharePoint list represents a run of an 87r*-Rejection of a release ticket (RLMxy) in Telefonica's UTS. A release in Telefonica's UTS is either a stand-alone release or it is associated with either a linkrevision in APT/Linkvis or with a NESO- or site-project (German: Standortprojekt) in Netsite.

A release can occur more than once in the list if there is more than one rejection run.

Unique Identifier / Key

The unique identifier of the SharePoint list is the field „Titel". It is calculated by concatenation of the Release ID and the date of the 87r*-Rejection — i.e. the value in the field 87r*:

[ReleaseID]_[Date of 87r]

Logic for adding new rows to the list

Option a): Adding the row manually. All fields must be edited manually.

Option b): Adding it automatically by a query of UTS_INTEGRATION_REJECTION_aktuell.xlsx. Add a new row in AREA_GU_Rejections_UTS for each row for which A is true:

Pseudo-Code:

Equivalent Boolean Expression:

Data Architecture

There are 2 stages in this flow architecture:

1. Stage 1 — Power BI: data transformations and processing
   2. Stage 2 — Power Automate: mapping, exporting, and updating the SharePoint list

flowchart LR
    XLS1[UTS_INTEGRATION_REJECTION_aktuell.xlsx]
    XLS2[UTS_RELEASES_aktuell.xlsb]
    XLS1 --> DF[Power BI Dataflow:
UTS_Integrations_Rejection_list_delta]
    XLS2 --> DF
    DF -->|"Merge on ReleaseID"| MERGED[merged table]
    MERGED -->|Right Outer Join SP| UPD[to_update]
    MERGED -->|Left Anti Join SP| IMP[to_import]
    SP[(SharePoint list
UTS Integration Rejection)] --> MERGED
    UPD --> RPT[Helper Power BI Report:
UTS_Integration_Rejections_Delta_Report]
    IMP --> RPT
    RPT --> XLS3[Excel export via
Office Script]
    XLS3 --> PA[Power Automate
SharePoint update]
    PA --> SP

    classDef src fill:#e3f2fd,stroke:#1976d2,stroke-width:1px
    classDef pbi fill:#fff3cd,stroke:#b85c00,stroke-width:1px
    classDef pa fill:#bbdefb,stroke:#1976d2,stroke-width:1px
    classDef sp fill:#c8e6c9,stroke:#2e7d32,stroke-width:1px
    class XLS1,XLS2 src
    class DF,MERGED,UPD,IMP,RPT pbi
    class XLS3,PA pa
    class SP sp

Power BI architecture

UTS_Integration_Rejection and UTS_RELEASES are available as dataflows in Power BI. In the UTS_Integrations_Rejection_list_delta dataflow:

• As a first step, both UTS files are merged together, using ReleaseID as the key.
• Based on the filter conditions provided, transformations are carried out on this table.
• The resulting table is then treated two ways:
  • to_update: UTS table merged with the SharePoint list using Title as the key and Right Outer Join — retains all rows from the SharePoint list and the matching rows from the Excel files.
  • to_import: UTS table merged with SharePoint list using Title as key and Left Anti Join — retains rows from the UTS files which do not have a matching Title in SharePoint.

The data from the dataflows is then used in a helper Power BI report — UTS_Integration_Rejections_Delta_Report. This is needed so we can export to Excel using the Power BI dataset connector and refresh remotely via an Office Script.

Workbook structure

Two Excel files are created — one for update and another for import. The workbook structure is critical:

• Only one sheet per workbook
• The table name must be Table_external1

The Office Script is saved in: BI Community → Admin channel → OfficeScripts.

Power Automate architecture

Details in der Original-Page noch in Ausarbeitung.

Related pages

• Power Automate Flows (Hub)
• RAN Overview — Excel to SharePoint List — gleicher Pattern fuer Netsite-NESO-Projekte

Verwandte Themen

Kuratiert — kann direkt im Editor ergänzt werden://

• RAN Overview — Excel to SharePoint List

Automatisch vorgeschlagen über gemeinsame Tags (power-automate, schnittstelle): 

• RAN Overview — Excel to SharePoint List

Status: Migriert — Team-Review ausstehend · Owner: (festlegen) · Letzter Review: 2026-06-11