RAN Overview — Excel to SharePoint List

Objective

To import into or update data in the RAN Overview SharePoint list from NLG_CUBE_aktuell.xlsx based on certain conditions.

Requirements from the requestor

Introduction

Each row in the SharePoint list RAN_Overview is the representation of a NESO-Project (7xxxxxxxx) in Telefonica's Netsite. NESO-Projects can be related to each other in Netsite by the Verknuepfungsgruppe (relationship group). This ID is the vkn_Gruppe_ID.

The Verknuepfungsgruppe consists usually of a main project, which is relevant for the acceptance, and a number of slave projects which are not relevant for acceptance but need to be administered too.

In the NRG program, NESO-Projects are usually additionally related to a Netsite Standortprojekt (site project 9xxxxxxxx).

The terms row / dataset / project might be used interchangeably. The terms column / field might be used interchangeably.

Unique Identifier

The unique identifier in the SharePoint list RAN_Overview is the field „Projekt-ID" — the project ID of a NESO-Project in Netsite.

Logic for adding new rows to the list

A row from NLG_Cube_aktuell.xlsb shall be added to RAN_Overview SP when either one of the following clauses evaluates to true:

First Clause — Identifying the master project:

Second Clause — Identifying the slave projects:

A project for which Condition 1 evaluates to true can be a member of a Verknuepfungsgruppe. All other members of this Verknuepfungsgruppe shall be added to the list, too.

Data Architecture

Two stages: Power BI for transformations, Power Automate for SharePoint updates.

flowchart LR
    NLG[NLG_CUBE_aktuell.xlsx
Dataflow]
    NLG -->|Master-Filter:
NE_Typ + Projekt_Art + ...| MASTERS[Master vkn_Gruppe_IDs]
    MASTERS -->|Inner Join| FULL[Master + Slave
NLG_Table]
    FULL -->|Inner Join SP-list
+ Binary-Key-Vergleich| UPD[to_update]
    FULL -->|Left Anti Join SP-list| IMP[to_import]
    SP[(SharePoint list
RAN_Overview)] --> UPD
    SP --> IMP
    UPD --> RPT[Helper PBI Report:
RAN_Overview_list_delta_report]
    IMP --> RPT
    RPT --> XLS[Excel via Office Script]
    XLS --> PA[Power Automate flow]
    PA --> SP

    classDef src fill:#e3f2fd,stroke:#1976d2,stroke-width:1px
    classDef pbi fill:#fff3cd,stroke:#b85c00,stroke-width:1px
    classDef sp fill:#c8e6c9,stroke:#2e7d32,stroke-width:1px
    class NLG src
    class MASTERS,FULL,UPD,IMP,RPT pbi
    class XLS,PA pbi
    class SP sp

Power BI architecture

A new dataflow RAN_Overview_list_delta is created in the MN DE Backoffice workspace, comparing NLG_CUBE with the SharePoint list:

1. First step: apply filter conditions to NLG_CUBE → list of master vkn_Gruppe_IDs.
   2. Use this list to filter the original NLG_CUBE (Inner Join with unfiltered NLG_CUBE) → list of master + slave IDs.
   3. The resulting table — NLG_Table — is processed two ways:

to_update

• Projekt-IDs from SharePoint list compared with Projekt-IDs in NLG_Table using Inner Join → only Projekt-IDs already existing in the SharePoint list.
• Unpivot all date columns in both tables.
• All date columns in both NLG_Table and RAN_Overview_list dynamically converted to whole numbers.
• All columns in both tables converted to text format and merged with $ as separator.
• Merged column converted into a Binary Key in a custom column.
• Compare binary keys for every Projekt-ID:
  • NLG_table[PID] = List[PID] AND NLG_Table[Key] <> List[Key]
• Result: list of Projekt-IDs from NLG_CUBE with different data vs RAN Overview list.
• Another Inner Join with the original NLG_Table → retrieve full rows.

to_import

• Projekt-IDs from NLG_Table and RAN Overview list compared using Left Anti Join → only rows present in NLG_Table without a match in RAN Overview list.

Workbook structure

Same pattern as UTS: two Excel files (one update, one import), only one sheet per workbook, table name Table_external1. Office Script saved in BI Community → Admin channel → OfficeScripts:

Operations and Maintenance

Any major change in the automation process requires a 4-step procedure:

1. Make changes in the delta dataflow

If the change request is to include a new column in the list:

• Create the item in the SharePoint list.
• Edit the delta dataflow → go to the RAN_Overview_list table.
• Include the new column in the Choose columns step.
• If new column is of date type: convert in the Calculated date step (use Date only option from Transform → Date, NOT change of data type to date).
• In NLG_table: include the new column in Choose columns. If date type — blank rows will be dynamically replaced with the dummy date in Replaced value step (no extra action needed).
• If new column is of date type → can skip the next steps.
• Save, close, refresh the dataflow → go to Step 2.

If new column is NOT date type:

• Go to delta_list table → include the column name (from RAN_Overview_list) in the Unpivoted other columns step expression window.
• In Inserted merged column step: merge all non-date columns with $ separator. Add new column name in the expression window, just before Attribute.

• In to_update table: add the column name (from NLG_Table) in Unpivoted other columns + in Inserted merged column step (same order).
• Save, close, refresh.

2. Change the Power BI dataset (re-publish)

• Download the pbix of the helper PBI report.
• Open it, refresh the tables, publish again.

3. Delete and re-create the sheet in helper Excel workbooks

• Go to BI Admin SharePoint → Files → powerbi_export_automate_list.
• Open RAN_Overview_list_update.xlsx and RAN_Overview_list_import.xlsx separately.
• Delete the sheet „RANOverviewlistdeltarepor".
• Click Data → Get Data → Power Platform → From Power BI (SPL TELE).
• In the panel on the right, search for RAN_Overview.
• Choose to_import or to_update by clicking on Insert Table. Select all columns.
• New sheet „RANOverviewlistdeltarepor" appears. Keep only this sheet — delete any other worksheet.
• Save and close.

4. Change the Power Automate flow

Three places to make changes:

1. „List rows present in a table — copy" step → select the Table name from the list.
   2. „Update item" step → choose the new column to be mapped (via Advanced parameters). Map the new field to the corresponding column from Excel.
   3. „List rows present in a table" step → select Table from dropdown. „Create item" step → do the mapping.

Additional requirements

• Folders to be created in the Documents folder in ABISoIP SharePoint for every combination of Projekt-ID_SiteID from the list.
• Each folder should contain 4 subfolders as specified.
• SP_Documentation field to be updated with the link to the master folder.
• Import-Logik-Section has been modified accordingly.

Change log

Related pages

• Power Automate Flows (Hub)
• UTS Integration Rejection — Excel to SharePoint List — same Pattern fuer UTS-Rejections

Verwandte Themen

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

• UTS Integration Rejection — Excel to SharePoint List

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

• UTS Integration Rejection — Excel to SharePoint List

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