Skip to Content

RAN Overview — Excel to SharePoint List

Version 2.1 by Dimitri Rupp on 2026/06/11 09:16
Warning
For security reasons, the document is displayed in restricted mode as it is not the current version. There may be differences and errors due to this.
On this page

RAN Overview — Excel to SharePoint List

Information

Objective: Import / update the RAN Overview SharePoint list from NLG_CUBE_aktuell.xlsx based on master/slave NESO-project clauses.
Stages: Power BI (delta dataflow + helper report + Excel export) → Power Automate (SharePoint list update).

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:

A = (GU_Projekt ∈ {*SPL*}
    ∧ NE_Typ ∈ {30, 31, 32, 33}
    ∧ Projekt_Art ∈ {Aufbau, Swap-Out, Kapazitaetsanpassung, Datenkorrektur, Umbau, Umschaltung}
    ∧ Ist_92 ∈ Ø
    ∧ Ist_56a ∉ Ø)

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.

Information

Notes:

  • *SPL* means "String contains SPL" — written as a regular expression.
  • The first clause identifies all master projects of a Verknuepfungsgruppe.
  • The second clause identifies all slave projects in the same Verknuepfungsgruppe.

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.
Warning

Limitation in Power Automate: Power Automate parses blank dates as a string. Workaround: replace all blank dates in NLG_Table with a dummy date — 01.01.1990 is used.

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:

function main(workbook: ExcelScript.Workbook) {
   const sheet = workbook.getFirstWorksheet();
    workbook.refreshAllDataConnections();
}

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.
Warning

Column-order critical: The order of columns in the Merge expression is crucial. Keep the same order in to_update table merge step too.

  • 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.
Information

The same procedure applies for removing a column too. Always keep a backup copy of the flow before making changes.

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

DateDescription
20 Feb 2024A new step added in the Power Automate flow to have a failure notification email
29 Mar 2024Flow modified to include the steps to have folders and subfolders created in SharePoint based on Projekt-ID_SiteID
04 Apr 2024Flow modified to update the links in SP_Documentation field with the folder link
19 Jun 2024Site Summary Update and Health Check Report flows changed — added the column Prog

Related pages

Information

Migration footer
Source: RAN_Overview_ExceltoSharePointlist (SharePoint)
Original author: Bukkerji Sreejith
Original last-modified: 2024-06-19
Migration date: 2026-05-24
Migrated by: Dimitri Rupp via ClaudeAI
Notes: EN-Original 1:1 (Objective, Introduction, Unique Identifier, Master/Slave-Klauseln, Power-BI-Architektur, Operations 4-Step-Procedure, Change-Log, Additional Requirements); Mermaid 2-Stage-Architektur ergaenzt; Boolean-Expression in lesbaren Code-Block; rechtschreibliche Korrekturen (Kapazitaetsanpassung); technische Field-/Table-Namen 1:1 belassen.

Verwandte Themen

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

Failed to execute the [velocity] macro. Cause: [The execution of the [velocity] script macro is not allowed in [xwiki:IT-Wiki.Makros.Verwandte-Themen.WebHome]. Check the rights of its last author or the parameters if it's rendered from another script.]. Click on this message for details.

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