Skip to Content
Last modified by Dimitri Rupp on 2026/06/11 09:30
Show last authors
1 {{box cssClass="floatinginfobox" title="**On this page**"}}
2 {{toc/}}
3 {{/box}}
4
5 = RAN Overview — Excel to SharePoint List =
6
7 {{info}}
8 **Objective:** Import / update the **RAN Overview SharePoint list** from (% style="font-family:monospace" %)NLG_CUBE_aktuell.xlsx(%%) based on master/slave NESO-project clauses.
9 **Stages:** Power BI (delta dataflow + helper report + Excel export) → Power Automate (SharePoint list update).
10 {{/info}}
11
12 == Objective ==
13
14 To **import** into or **update** data in the **RAN Overview SharePoint list** from (% style="font-family:monospace" %)NLG_CUBE_aktuell.xlsx(%%) based on certain conditions.
15
16 == Requirements from the requestor ==
17
18 === Introduction ===
19
20 Each row in the SharePoint list **RAN_Overview** is the representation of a **NESO-Project** ((% style="font-family:monospace" %)7xxxxxxxx(%%)) in **Telefonica's Netsite**. NESO-Projects can be **related to each other** in Netsite by the **Verknuepfungsgruppe** (relationship group). This ID is the (% style="font-family:monospace" %)vkn_Gruppe_ID(%%).
21
22 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.
23
24 In the **NRG program**, NESO-Projects are usually additionally related to a **Netsite Standortprojekt** (site project (% style="font-family:monospace" %)9xxxxxxxx(%%)).
25
26 The terms **row / dataset / project** might be used interchangeably. The terms **column / field** might be used interchangeably.
27
28 === Unique Identifier ===
29
30 The **unique identifier** in the SharePoint list **RAN_Overview** is the field (% style="font-family:monospace" %)**„Projekt-ID"**(%%) — the **project ID** of a **NESO-Project** in Netsite.
31
32 === Logic for adding new rows to the list ===
33
34 A row from (% style="font-family:monospace" %)NLG_Cube_aktuell.xlsb(%%) shall be added to **RAN_Overview SP** when **either one of the following clauses** evaluates to **true**:
35
36 **First Clause — Identifying the master project:**
37
38 {{code}}
39 A = (GU_Projekt ∈ {*SPL*}
40 ∧ NE_Typ ∈ {30, 31, 32, 33}
41 ∧ Projekt_Art ∈ {Aufbau, Swap-Out, Kapazitaetsanpassung, Datenkorrektur, Umbau, Umschaltung}
42 ∧ Ist_92 ∈ Ø
43 ∧ Ist_56a ∉ Ø)
44 {{/code}}
45
46 **Second Clause — Identifying the slave projects:**
47
48 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.
49
50 {{info}}
51 **Notes:**
52 * (% style="font-family:monospace" %)*SPL*(%%) means "String contains SPL" — written as a regular expression.
53 * The **first clause** identifies all **master projects** of a Verknuepfungsgruppe.
54 * The **second clause** identifies all **slave projects** in the same Verknuepfungsgruppe.
55 {{/info}}
56
57 == Data Architecture ==
58
59 Two stages: **Power BI** for transformations, **Power Automate** for SharePoint updates.
60
61 {{mermaid}}
62 flowchart LR
63 NLG[NLG_CUBE_aktuell.xlsx<br/>Dataflow]
64 NLG -->|Master-Filter:<br/>NE_Typ + Projekt_Art + ...| MASTERS[Master vkn_Gruppe_IDs]
65 MASTERS -->|Inner Join| FULL[Master + Slave<br/>NLG_Table]
66 FULL -->|Inner Join SP-list<br/>+ Binary-Key-Vergleich| UPD[to_update]
67 FULL -->|Left Anti Join SP-list| IMP[to_import]
68 SP[(SharePoint list<br/>RAN_Overview)] --> UPD
69 SP --> IMP
70 UPD --> RPT[Helper PBI Report:<br/>RAN_Overview_list_delta_report]
71 IMP --> RPT
72 RPT --> XLS[Excel via Office Script]
73 XLS --> PA[Power Automate flow]
74 PA --> SP
75
76 classDef src fill:#e3f2fd,stroke:#1976d2,stroke-width:1px
77 classDef pbi fill:#fff3cd,stroke:#b85c00,stroke-width:1px
78 classDef sp fill:#c8e6c9,stroke:#2e7d32,stroke-width:1px
79 class NLG src
80 class MASTERS,FULL,UPD,IMP,RPT pbi
81 class XLS,PA pbi
82 class SP sp
83 {{/mermaid}}
84
85 === Power BI architecture ===
86
87 A new dataflow (% style="font-family:monospace" %)RAN_Overview_list_delta(%%) is created in the **MN DE Backoffice** workspace, comparing **NLG_CUBE** with the SharePoint list:
88
89 1. First step: apply filter conditions to (% style="font-family:monospace" %)NLG_CUBE(%%) → list of **master (% style="font-family:monospace" %)vkn_Gruppe_IDs(%%)**.
90 2. Use this list to filter the original (% style="font-family:monospace" %)NLG_CUBE(%%) (Inner Join with unfiltered NLG_CUBE) → list of **master + slave IDs**.
91 3. The resulting table — (% style="font-family:monospace" %)NLG_Table(%%) — is processed two ways:
92
93 ==== to_update ====
94
95 * **Projekt-IDs** from SharePoint list compared with Projekt-IDs in (% style="font-family:monospace" %)NLG_Table(%%) using **Inner Join** → only Projekt-IDs **already existing** in the SharePoint list.
96 * **Unpivot all date columns** in both tables.
97 * All **date columns** in both (% style="font-family:monospace" %)NLG_Table(%%) and (% style="font-family:monospace" %)RAN_Overview_list(%%) **dynamically converted to whole numbers**.
98 * All columns in both tables converted to **text format** and merged with **(% style="font-family:monospace" %)$(%%)** as separator.
99 * Merged column converted into a **Binary Key** in a custom column.
100 * Compare binary keys for every Projekt-ID:
101 ** (% style="font-family:monospace" %)NLG_table[PID] = List[PID] AND NLG_Table[Key] <> List[Key](%%)
102 * Result: list of Projekt-IDs from NLG_CUBE with **different data** vs RAN Overview list.
103 * Another **Inner Join** with the original NLG_Table → retrieve full rows.
104
105 ==== to_import ====
106
107 * 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.
108
109 {{warning}}
110 **Limitation in Power Automate:** Power Automate parses **blank dates as a string**. **Workaround:** replace all blank dates in (% style="font-family:monospace" %)NLG_Table(%%) with a **dummy date** — **(% style="font-family:monospace" %)01.01.1990(%%)** is used.
111 {{/warning}}
112
113 === Workbook structure ===
114
115 Same pattern as UTS: two Excel files (one update, one import), **only one sheet per workbook**, table name **(% style="font-family:monospace" %)Table_external1(%%)**. Office Script saved in BI Community → Admin channel → OfficeScripts:
116
117 {{code language="javascript"}}
118 function main(workbook: ExcelScript.Workbook) {
119 const sheet = workbook.getFirstWorksheet();
120 workbook.refreshAllDataConnections();
121 }
122 {{/code}}
123
124 == Operations and Maintenance ==
125
126 Any major change in the automation process requires a **4-step procedure**:
127
128 === 1. Make changes in the delta dataflow ===
129
130 If the change request is to **include a new column** in the list:
131
132 * Create the item in the SharePoint list.
133 * Edit the delta dataflow → go to the (% style="font-family:monospace" %)RAN_Overview_list(%%) table.
134 * Include the new column in the **Choose columns** step.
135 * 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).
136 * In (% style="font-family:monospace" %)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).
137 * If new column **is** of date type → can skip the next steps.
138 * Save, close, refresh the dataflow → go to **Step 2**.
139
140 If new column is **NOT date** type:
141
142 * Go to (% style="font-family:monospace" %)delta_list(%%) table → include the column name (from (% style="font-family:monospace" %)RAN_Overview_list(%%)) in the **Unpivoted other columns** step expression window.
143 * In **Inserted merged column** step: merge all non-date columns with (% style="font-family:monospace" %)$(%%) separator. **Add new column name in the expression window**, just before **Attribute**.
144
145 {{warning}}
146 **Column-order critical:** The order of columns in the **Merge** expression is crucial. Keep the **same order** in (% style="font-family:monospace" %)to_update(%%) table merge step too.
147 {{/warning}}
148
149 * In (% style="font-family:monospace" %)to_update(%%) table: add the column name (from (% style="font-family:monospace" %)NLG_Table(%%)) in **Unpivoted other columns** + in **Inserted merged column** step (same order).
150 * Save, close, refresh.
151
152 === 2. Change the Power BI dataset (re-publish) ===
153
154 * **Download the pbix** of the helper PBI report.
155 * Open it, **refresh the tables**, **publish** again.
156
157 === 3. Delete and re-create the sheet in helper Excel workbooks ===
158
159 * Go to **BI Admin SharePoint → Files → powerbi_export_automate_list**.
160 * Open (% style="font-family:monospace" %)RAN_Overview_list_update.xlsx(%%) and (% style="font-family:monospace" %)RAN_Overview_list_import.xlsx(%%) separately.
161 * Delete the sheet (% style="font-family:monospace" %)„RANOverviewlistdeltarepor"(%%).
162 * Click **Data → Get Data → Power Platform → From Power BI (SPL TELE)**.
163 * In the panel on the right, search for **RAN_Overview**.
164 * Choose **to_import** or **to_update** by clicking on **Insert Table**. Select all columns.
165 * New sheet (% style="font-family:monospace" %)„RANOverviewlistdeltarepor"(%%) appears. **Keep only this sheet** — delete any other worksheet.
166 * Save and close.
167
168 === 4. Change the Power Automate flow ===
169
170 Three places to make changes:
171
172 1. **„List rows present in a table — copy"** step → select the **Table name** from the list.
173 2. **„Update item"** step → choose the **new column** to be mapped (via **Advanced parameters**). Map the new field to the corresponding column from Excel.
174 3. **„List rows present in a table"** step → select Table from dropdown. **„Create item"** step → do the mapping.
175
176 {{info}}
177 The same procedure applies **for removing a column** too. **Always keep a backup copy** of the flow before making changes.
178 {{/info}}
179
180 === Additional requirements ===
181
182 * **Folders** to be created in the **Documents** folder in **ABISoIP SharePoint** for **every combination of Projekt-ID_SiteID** from the list.
183 * Each folder should contain **4 subfolders** as specified.
184 * **(% style="font-family:monospace" %)SP_Documentation(%%) field** to be updated with the **link to the master folder**.
185 * **Import-Logik-Section** has been modified accordingly.
186
187 == Change log ==
188
189 |=Date|=Description
190 |**20 Feb 2024**|A new step added in the Power Automate flow to have a **failure notification email**
191 |**29 Mar 2024**|Flow modified to include the steps to have **folders and subfolders created** in SharePoint based on (% style="font-family:monospace" %)Projekt-ID_SiteID(%%)
192 |**04 Apr 2024**|Flow modified to **update the links** in (% style="font-family:monospace" %)SP_Documentation(%%) field with the folder link
193 |**19 Jun 2024**|**Site Summary Update** and **Health Check Report** flows changed — added the column //Prog//
194
195 == Related pages ==
196
197 * [[Power Automate Flows (Hub)>>doc:IT-Wiki.01-Data.Exchange-und-Movement.Schnittstellen.WebHome]]
198 * [[UTS Integration Rejection — Excel to SharePoint List>>doc:IT-Wiki.01-Data.Exchange-und-Movement.Schnittstellen.UTS_Integration_Rejection_ExceltoSharePointList.WebHome]] — same Pattern fuer UTS-Rejections
199
200 ----
201
202 == Verwandte Themen ==
203
204 //Kuratiert — kann direkt im Editor ergänzt werden://
205
206 * [[UTS Integration Rejection — Excel to SharePoint List>>doc:IT-Wiki.01-Data.Exchange-und-Movement.Schnittstellen.UTS_Integration_Rejection_ExceltoSharePointList.WebHome]]
207
208 {{include reference="IT-Wiki.Makros.Verwandte-Themen.WebHome"/}}
209
210 ----
211 **Status:** Migriert — Team-Review ausstehend · **Owner:** //(festlegen)// · **Letzter Review:** 2026-06-11