Forum Discussion

BMM27's avatar
BMM27
Helper I
2 years ago
Solved

Setting Scheduled update on Dynamic source query

Hi, I've created a data source that retrieves files from a folder and performs actions based on the file name. However, when I published it to the Power BI service, scheduled updates are disabled. I...
Need Help
  • BMM27's avatar
    BMM27
    2 years ago

    Hi, here is my solution. For anyone who is going to have same trouble, here is the code.
    This code connects to a sharepoint folder, search by name different files, and unify all files into one table.

    let
    // Source
    Source = SharePoint.Files("https://SharepointSite", [ApiVersion = 15]),
    FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "AAA") or Text.StartsWith([Name], "BBB") and [Folder Path] = "https://sharepointfolder"),

    // Data process function
    ProcessFile = (file as record) =>
    let
    name = Text.BeforeDelimiter(file[Name], ".xlsx"),
    content = file[Content],
    workbook = Excel.Workbook(content),

    // Sheet names
    sheetNames = Table.Column(workbook, "Item"),
    origen = if List.Contains(sheetNames, "aa") then "AA" else if Text.StartsWith(name, "bb") then "BB" else "CC",
    sheetName = if origen = "aa" then "AA" else if origen = "aa" then "BB" else "cc",

    sheet = try workbook{[Item=sheetName, Kind="Sheet"]}[Data] otherwise null,
    table = if sheet = null then null else Table.Skip(sheet, 1)
    in
    table,

    // Process each file
    ProcessedFiles = Table.AddColumn(FilteredFiles, "ProcessedContent", each ProcessFile(_)),

    // Filter Null
    NonNullTables = Table.SelectRows(ProcessedFiles, each [ProcessedContent] <> null),

    // Expand and combine
    ExpandedTables = Table.ExpandTableColumn(NonNullTables, "ProcessedContent", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),

    #"Removed Columns" = Table.RemoveColumns(ExpandedTables,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})
    in
    #"Removed Columns"

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi BMM27 ,

Based on your problems, here are my answers.

Firstly I think you can consider using parameters to specify the file paths Instead of dynamically generating the file path within the query. It will help Power BI service to recognize and access these files. I think you can read this document: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

 If your SharePoint site is not accessible directly by the Power BI service, ensure that you have a data gateway installed and configured to facilitate the connection between Power BI service and your SharePoint site.

After publishing your report to the Power BI service, ensure that the dataset's credentials and settings are correctly configured to allow for scheduled refreshes. This includes setting up the correct authentication method for SharePoint. Maybe this document can help you: How to configure Power BI report scheduled refresh - Power BI | Microsoft Learn

 

 

 

Best Regards

Yilong Zhou

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

  • BMM27's avatar
    BMM27
    Helper I
    2 years ago

    Hi, here is my solution. For anyone who is going to have same trouble, here is the code.
    This code connects to a sharepoint folder, search by name different files, and unify all files into one table.

    let
    // Source
    Source = SharePoint.Files("https://SharepointSite", [ApiVersion = 15]),
    FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "AAA") or Text.StartsWith([Name], "BBB") and [Folder Path] = "https://sharepointfolder"),

    // Data process function
    ProcessFile = (file as record) =>
    let
    name = Text.BeforeDelimiter(file[Name], ".xlsx"),
    content = file[Content],
    workbook = Excel.Workbook(content),

    // Sheet names
    sheetNames = Table.Column(workbook, "Item"),
    origen = if List.Contains(sheetNames, "aa") then "AA" else if Text.StartsWith(name, "bb") then "BB" else "CC",
    sheetName = if origen = "aa" then "AA" else if origen = "aa" then "BB" else "cc",

    sheet = try workbook{[Item=sheetName, Kind="Sheet"]}[Data] otherwise null,
    table = if sheet = null then null else Table.Skip(sheet, 1)
    in
    table,

    // Process each file
    ProcessedFiles = Table.AddColumn(FilteredFiles, "ProcessedContent", each ProcessFile(_)),

    // Filter Null
    NonNullTables = Table.SelectRows(ProcessedFiles, each [ProcessedContent] <> null),

    // Expand and combine
    ExpandedTables = Table.ExpandTableColumn(NonNullTables, "ProcessedContent", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),

    #"Removed Columns" = Table.RemoveColumns(ExpandedTables,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})
    in
    #"Removed Columns"