Forum Discussion

jbrown184's avatar
jbrown184
Helper I
1 year ago

Safety Culture API dynamic data source power bi online

 

I am having this issue:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.]

  • Data source for Query1

Below is my query.

 

 

 

let
// Initialize the base URL and authorization token
baseUrl = "/feed/inspection_items", // Use relative path
authorizationToken = "Bearer API_Token",

// Define initial parameters (modify as needed)
modifiedAfter = "2024-10-27T23:14:23.000Z",
template = "template_f65d35f47bd04b379836b2b35bf0a1cd",
archived = "false",
completed = "true",
includeInactive = "false",

// Construct the query string
queryString = "?modified_after=" & modifiedAfter &
"&template=" & template &
"&archived=" & archived &
"&completed=" & completed &
"&include_inactive=" & includeInactive,

// Define the relative request URL
relativeUrl = baseUrl & queryString,

// Full URL based on the current domain (adjust as per your service base URL)
url = "https://api.safetyculture.io" & relativeUrl, // Ensure this is correct in the service context

// Function to fetch data from API and handle pagination
fetchData = (url as text) =>
let
// Send HTTP request
response = Json.Document(Web.Contents(url, [
Headers = [
#"accept" = "application/json",
#"authorization" = authorizationToken
]
])),

// Extract the data from the response
data = response[data],

// Check if there is a next page URL
nextPage = try response[metadata][next_page] otherwise null
in
// Return data and next page URL
[data = data, nextPage = nextPage],

// Fetch the first page of data
firstPage = fetchData(url),

// If there is a next page, fetch subsequent pages
allData = List.Generate(
() => [data = firstPage[data], nextPage = firstPage[nextPage]],
each List.Count([data]) > 0 and [nextPage] <> null,
each let nextPageUrl = "https://api.safetyculture.io" & [nextPage] in
fetchData(nextPageUrl),
each [data]
),

// Combine all pages of data into a single list
combinedData = List.Combine(allData),
#"Converted to Table" = Table.FromList(combinedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "item_id", "audit_id", "item_index", "template_id", "parent_id", "organisation_id", "created_at", "modified_at", "type", "category", "category_id", "parent_ids", "primeelement_id", "primeelement_index", "label", "response", "response_id", "response_set_id", "is_failed_response", "comment", "media_files", "media_ids", "media_hypertext_reference", "score", "max_score", "score_percentage", "combined_score", "combined_max_score", "combined_score_percentage", "mandatory", "inactive", "location_latitude", "location_longitude"}, {"Column1.id", "Column1.item_id", "Column1.audit_id", "Column1.item_index", "Column1.template_id", "Column1.parent_id", "Column1.organisation_id", "Column1.created_at", "Column1.modified_at", "Column1.type", "Column1.category", "Column1.category_id", "Column1.parent_ids", "Column1.primeelement_id", "Column1.primeelement_index", "Column1.label", "Column1.response", "Column1.response_id", "Column1.response_set_id", "Column1.is_failed_response", "Column1.comment", "Column1.media_files", "Column1.media_ids", "Column1.media_hypertext_reference", "Column1.score", "Column1.max_score", "Column1.score_percentage", "Column1.combined_score", "Column1.combined_max_score", "Column1.combined_score_percentage", "Column1.mandatory", "Column1.inactive", "Column1.location_latitude", "Column1.location_longitude"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.id", "Column1.item_id", "Column1.audit_id", "Column1.item_index", "Column1.template_id", "Column1.parent_id", "Column1.organisation_id", "Column1.created_at", "Column1.modified_at", "Column1.type", "Column1.category", "Column1.category_id", "Column1.parent_ids", "Column1.primeelement_id", "Column1.primeelement_index", "Column1.response_id", "Column1.response_set_id", "Column1.is_failed_response", "Column1.comment", "Column1.media_files", "Column1.media_ids", "Column1.media_hypertext_reference", "Column1.score", "Column1.max_score", "Column1.score_percentage", "Column1.combined_score", "Column1.combined_max_score", "Column1.combined_score_percentage", "Column1.mandatory", "Column1.inactive", "Column1.location_latitude", "Column1.location_longitude"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.label", "Question"}, {"Column1.response", "Response"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Question] <> "Title Page")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
#"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([Question], "Fecha") then [Index] else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}),
#"Filled Down" = Table.FillDown(#"Removed Columns1",{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Question]), "Question", "Response"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom", "if response is |Yes / Sí|"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Conducted on / Fecha", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha.1", "Conducted on / Fecha.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Conducted on / Fecha.1", type date}, {"Conducted on / Fecha.2", type time}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type",{"Conducted on / Fecha.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns3",{{"Conducted on / Fecha.1", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Conducted on / Fecha.1", "Date"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "Date", "Date - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Date - Copy", "Date - Copy - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date - Copy", "Week"}, {"Date - Copy - Copy", "Year"}}),
#"Calculated Week of Year" = Table.TransformColumns(#"Renamed Columns2",{{"Week", Date.WeekOfYear, Int64.Type}}),
#"Extracted Year" = Table.TransformColumns(#"Calculated Week of Year",{{"Year", Date.Year, Int64.Type}})
in
#"Extracted Year"

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi jbrown184 ,
    Based on your description and the error message provided, it points out that you are using a dynamic data source, and power bi generally does not support refreshing of dynamic data sources. If you want to do a refresh, you can use the method mentioned in the link to modify the above code using RelativePath and query options in conjunction with the Web.Contents M function and a query that references the Power Query parameter. Here is an attempt to modify the above code

    let
    // Define parameters
    baseUrl = "https://api.safetyculture.io",
    relativePath = "/feed/inspection_items",
    authorizationToken = "Bearer API_Token",
    modifiedAfter = "2024-10-27T23:14:23.000Z",
    template = "template_f65d35f47bd04b379836b2b35bf0a1cd",
    archived = "false",
    completed = "true",
    includeInactive = "false",

    // Construct the query string
    query = [
        modified_after = modifiedAfter,
        template = template,
        archived = archived,
        completed = completed,
        include_inactive = includeInactive
    ],

    // Use Web.Contents function
    fetchData = (relativePath as text, query as record) =>
    let
        response = Json.Document(Web.Contents(baseUrl, [
            RelativePath = relativePath,
            Query = query,
            Headers = [
                #"accept" = "application/json",
                #"authorization" = authorizationToken
            ]
        ])),
        data = response[data],
        nextPage = try response[metadata][next_page] otherwise null
    in
        [data = data, nextPage = nextPage],

    // Fetch the first page of data
    firstPage = fetchData(relativePath, query),

    // If there is a next page, fetch subsequent pages
    allData = List.Generate(
        () => [data = firstPage[data], nextPage = firstPage[nextPage]],
        each List.Count([data]) > 0 and [nextPage] <> null,
        each let nextPageUrl = [nextPage] in
        fetchData(relativePath, [modified_after = nextPageUrl]),
        each [data]
    ),
// Combine all pages of data into a single list
    combinedData = ...

    The above code is just an example, you can follow this logic to make a change to the original code, you can also refer to the following documentation

    Power Query - Dynamic Data Source and Web.Contents() - Hat Full of Data

    Web.Contents - PowerQuery M | Microsoft Learn

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly