Recently, I struggled to import data from NC Tables to Microsoft Excel or Power BI and couldn’t find the right documentation. Now that I have figured it out, I would like to share the solution with the community so that you don’t have to conduct in-depth research again. 
1. Prerequisites
Authentication
-
If your Nextcloud instance requires app passwords for external authentication, ensure you have one created.
-
If your Nextcloud instance uses standard username/password authentication, you can directly use your credentials in Excel or Power BI.
2. Importing Data into Excel
Step 1: Open Excel and Get Data
-
Open Excel.
-
Go to Data → Get Data → From Other Sources → From Web.
Step 2: Enter API URL
-
Enter the following URL (replace
{tableId}with your Nextcloud Tables table ID and{your-nextcloud-domain}with your domain):https://{your-nextcloud-domain}/apps/tables/api/1/tables/{tableId}/rows -
Click OK.
Step 3: Set Up Authentication
-
Select Basic authentication.
-
Enter your Nextcloud username.
-
Enter your password (or app password if required).
-
Click Connect.
Step 4: Open Power Query Editor
-
After loading, the Power Query Editor will open.
-
Click Advanced Editor (under Home → Advanced Editor).
Step 5: Insert M Code
Replace the existing code with the following (adjust {your-nextcloud-domain} and {tableId}):
let
// 1. Fetch data from /tables/{tableId}/rows
DatenJson = Json.Document(Web.Contents("https://{your-nextcloud-domain}/apps/tables/api/1/tables/{tableId}/rows")),
// 2. Fetch headers and selectionOptions from /tables/{tableId}/columns
HeaderJson = Json.Document(Web.Contents("https://{your-nextcloud-domain}/apps/tables/api/1/tables/{tableId}/columns")),
// Extract headers from the "title" field
HeaderListe = List.Transform(HeaderJson, each [title]),
// Extract columnIds from headers
ColumnIds = List.Transform(HeaderJson, each [id]),
// Create a dictionary for selectionOptions
SelectionOptionsDict = List.Accumulate(
HeaderJson,
[],
(state, current) =>
if Record.HasFields(current, "selectionOptions") and List.Count(current[selectionOptions]) > 0 then
let
columnIdText = Text.From(current[id]),
optionsList = List.Transform(current[selectionOptions], (option) => {option[id], option[label]})
in
Record.AddField(state, columnIdText, optionsList)
else
state
),
// Create a dictionary for column types
ColumnTypesDict = List.Accumulate(
HeaderJson,
[],
(state, current) =>
let
columnIdText = Text.From(current[id])
in
Record.AddField(state, columnIdText, {current[type], current[subtype]})
),
// 3. Create a list of lists with the `value` fields
ValueLists = List.Transform(DatenJson, (row) =>
let
// Create a record from data entries
DataRecord = List.Accumulate(
row[data],
[],
(state, current) => Record.AddField(state, Text.From(current[columnId]), current[value])
),
// Fill missing values with null and process different data types
FilledValues = List.Transform(ColumnIds, (columnId) =>
let
columnIdText = Text.From(columnId),
itemValue = if Record.HasFields(DataRecord, columnIdText) then Record.Field(DataRecord, columnIdText) else null,
columnType = Record.FieldOrDefault(ColumnTypesDict, columnIdText, {null, null}),
columnTypeName = columnType{0},
columnSubtype = columnType{1},
// Process different data types
finalValue =
if itemValue = null then null
// Text with subtype "link" - parse JSON
else if columnTypeName = "text" and columnSubtype = "link" then
try
let
linkJson = Json.Document(itemValue)
in
linkJson[value]
otherwise itemValue
// Selection with subtype "multi" - multiple values
else if columnTypeName = "selection" and columnSubtype = "multi" then
if itemValue is list then
let
options = Record.FieldOrDefault(SelectionOptionsDict, columnIdText, null)
in
if options <> null then
Text.Combine(
List.Transform(itemValue, (id) =>
let
matchingOption = List.First(List.Select(options, (option) => option{0} = id), null)
in
if matchingOption <> null then matchingOption{1} else Text.From(id)
),
", "
)
else
Text.Combine(List.Transform(itemValue, Text.From), ", ")
else null
// Selection (single) - single value
else if columnTypeName = "selection" and columnSubtype <> "check" then
if itemValue is number then
let
options = Record.FieldOrDefault(SelectionOptionsDict, columnIdText, null)
in
if options <> null then
let
matchingOption = List.First(List.Select(options, (option) => option{0} = itemValue), null)
in
if matchingOption <> null then matchingOption{1} else itemValue
else itemValue
else itemValue
// Selection with subtype "check" - Boolean
else if columnTypeName = "selection" and columnSubtype = "check" then
if itemValue = "true" then true
else if itemValue = "false" then false
else null
// Usergroup - can be person/group/team
else if columnTypeName = "usergroup" then
if itemValue is list and List.Count(itemValue) > 0 then
Text.Combine(
List.Transform(itemValue, (user) => user[displayName]),
", "
)
else null
// All other types
else itemValue
in
finalValue
)
in
FilledValues
),
// 4. Create a table from the `value` lists and headers
Tabelle = Table.FromRecords(
List.Transform(ValueLists, (valueList) => Record.FromList(valueList, HeaderListe)),
HeaderListe
)
in
Tabelle
Step 6: Load Data
-
Click Done in the Advanced Editor.
-
Click Close & Load to import the data into Excel.
Step 7: Refresh Data
- To refresh the data, right-click the table and select Refresh.
3. Importing Data into Power BI
Step 1: Open Power BI and Get Data
-
Open Power BI Desktop.
-
Click Get Data → Web.
Step 2: Enter API URL
-
Enter the same URL as in Excel:
https://{your-nextcloud-domain}/apps/tables/api/1/tables/{tableId}/rows -
Click OK.
Step 3: Set Up Authentication
-
Select Basic authentication.
-
Enter your Nextcloud username.
-
Enter your password (or app password if required).
-
Click Connect.
Step 4: Transform Data in Power Query Editor
- Follow Steps 4–6 from the Excel section to insert the M code and load the data.
Step 5: Model and Visualize Data
- Use the imported data to create visualizations in Power BI.
Step 6: Refresh Data
- To refresh the data, click Home → Refresh or set up a scheduled refresh in the Power BI Service.
4. Summary of Steps
- Set up authentication (app password if required)
- Open Excel/Power BI and select Web data source
- Enter API URL and authentication details
- Insert M code in the Advanced Editor
- Load and transform data
- Refresh data as needed
Notes
-
Replace
{your-nextcloud-domain}and{tableId}with your actual values. -
This guide works for Nextcloud Tables with both Excel and Power BI.
-
For environments with standard username/password authentication, you can directly use your credentials.