"This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service"

Question

So i have a problem when try to use refresh schedule menu from Power BI. The problem is 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.

I tried to collect my dataset using paginated API. However, I think because this process is likely dynamic so the Power BI Service cannot do the scheduling refresh. How to fix that? Thanks in advance.

let
    Source = Json.Document(Web.Contents("https://semenbeku.rcve.in/api/semen-beku-produksis?pagination[page]=1")),
    metadata = Source[meta],
    pagination = metadata[pagination],
    totalpages = pagination[pageCount],
    List = {1..totalpages},
    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type text}}),
    #"Get Page" = Table.AddColumn(#"Changed Type", "Data", each 
        let
            page = [Column1],
            Source = Json.Document(Web.Contents("https://semenbeku.rcve.in/api/semen-beku-produksis?pagination[page]=" & page)),
            data = Source[data],
            #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"attributes"}),
            #"Expanded attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "attributes", {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"}, 
            {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"})
        in
            #"Expanded attributes"
    ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Get Page", "Data", {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"}, {"Data.eartag", "Data.tgl", "Data.kode_straw", "Data.jenis", "Data.rumpun", "Data.sexing", "Data.jumlah", "Data.createdAt", "Data.updatedAt", "Data.publishedAt"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data", {{"Data.createdAt", type datetime}, {"Data.updatedAt", type datetime}, {"Data.publishedAt", type datetime}})
in
    #"Changed Type1"

Answer 1

This is a common error to get if you are using the Web.Contents function in Power Query and use a dynamic endpoint, instead of using the Web.Contents as it should!

Here is a fixed code snippet:

let
    Source = Json.Document(Web.Contents("https://semenbeku.rcve.in/api/semen-beku-produksis?pagination[page]=1")),
    metadata = Source[meta],
    pagination = metadata[pagination],
    totalpages = pagination[pageCount],
    List = {1..totalpages},
    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type text}}),
    #"Get Page" = Table.AddColumn(#"Changed Type", "Data", each 
        let
            page = [Column1],
            Source = Json.Document(Web.Contents("https://semenbeku.rcve.in/api/semen-beku-produksis,[Query = ["pagination[page]"= page]])),
            data = Source[data],
            #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"attributes"}),
            #"Expanded attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "attributes", {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"}, 
            {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"})
        in
            #"Expanded attributes"
    ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Get Page", "Data", {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"}, {"Data.eartag", "Data.tgl", "Data.kode_straw", "Data.jenis", "Data.rumpun", "Data.sexing", "Data.jumlah", "Data.createdAt", "Data.updatedAt", "Data.publishedAt"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data", {{"Data.createdAt", type datetime}, {"Data.updatedAt", type datetime}, {"Data.publishedAt", type datetime}})
in
    #"Changed Type1"

Note that the semantics of a query parameter that includes a bracket is unknown to me, but this is how you would structure it. The key is the Web.Contents function takes an optional options record that can carry these extra "dynamic" components, while the request URL itself remains static in the expression.

See more here: https://learn.microsoft.com/en-us/powerquery-m/web-contents

Edit: You can perhaps also just use RelativePath instead of Query:

let
    Source = Json.Document(Web.Contents("https://semenbeku.rcve.in/api/semen-beku-produksis?pagination[page]=1")),
    metadata = Source[meta],
    pagination = metadata[pagination],
    totalpages = pagination[pageCount],
    List = {1..totalpages},
    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type text}}),
    #"Get Page" = Table.AddColumn(#"Changed Type", "Data", each 
        let
            page = [Column1],
            Source = Json.Document(Web.Contents("https://semenbeku.rcve.in/api/semen-beku-produksis,[RelativePath = "?pagination[page]=" & page]])),
            data = Source[data],
            #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"attributes"}),
            #"Expanded attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "attributes", {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"}, 
            {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"})
        in
            #"Expanded attributes"
    ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Get Page", "Data", {"eartag", "tgl", "kode_straw", "jenis", "rumpun", "sexing", "jumlah", "createdAt", "updatedAt", "publishedAt"}, {"Data.eartag", "Data.tgl", "Data.kode_straw", "Data.jenis", "Data.rumpun", "Data.sexing", "Data.jumlah", "Data.createdAt", "Data.updatedAt", "Data.publishedAt"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data", {{"Data.createdAt", type datetime}, {"Data.updatedAt", type datetime}, {"Data.publishedAt", type datetime}})
in
    #"Changed Type1"