Refresh in PowerBI Service not working due to dynamic data source (File.Contents)

[sgupta22]

I have a Power BI report that uses a dynamic data source through the File.Contents.  I understand that dynamic data sources do not get refreshed from Power BI Service but one can use the RelativePath parameter.  However, this didn't work for me and I get the following error message: 

Would be great if someone knows if there is a solution to this problem.

Cheers

Shashank

@Ashish_Mathur 

[lbendlin]

RelativePath  is not a feature of File.Contents.

File.Contents - PowerQuery M | Microsoft Learn

[sgupta22]

Thanks for your reply Ibendlin.

Is there a similar workaround for File.Contents? Or is it simply not possible to have a data refresh through PowerBI service when a dynamic data source is being used in File.Contents?

Regards

Shashank

[lbendlin]

Accessing files via File.Contents is considered bad practice. Use the Folder connector or - better yet - the Sharepoint Folder connector.  That takes care of the RelativePath part automatically.

[sgupta22]

Thanks Ibendlin.  I tried doing this but the issue that I am facing is that I am using a parameter which calls data from a specific file from the folder.  The way I tried doing the folder connector seems like it calls data from all the files at once. 

[lbendlin]

You can incorporate the parameter in the next steps after you connected to the folder.

[sgupta22]

Hi Ibendlin,

Sorry to keep bothering you with this but I've struggled to incorporate your suggestions (pls excuse my limited knowledge of Power BI/ Power Query).

Reproducing my current code below and would be grateful if you can suggest edits/ how you would do it differently. "H:\My Drive\Work\HFA" is the folder that I am trying to connect to and the "Company" is the parameter that calls the specific file based on my choice of company.

Would totally understand if this is too much to ask.

Regards

Shashank

let
    Source = Excel.Workbook(File.Contents(Text.Combine({"H:\My Drive\Work\HFA\HFA - ",Company,".xlsm"})),null, true),
    t_Workings_Table = Source{[Item="t_Data",Kind="Table"]}[Data],
    columnList = Table.ColumnNames(t_Workings_Table),
    #"Changed Type" = Table.TransformColumnTypes(t_Workings_Table,{{columnList {0}, type any}, {columnList {1}, type text}, {columnList {2}, type any}, {columnList {3}, type any}, {columnList {4}, type any}, {columnList {5}, type any}, {columnList {6}, type any}, {columnList {7}, type any}, {columnList {8}, type any}, {columnList {9}, type any}, {columnList {10}, type any}, {columnList {11}, type any}, {columnList {12}, type any}, {columnList {13}, type any}, {columnList {14}, type any}, {columnList {15}, type any}, {columnList {16}, type any}, {columnList {17}, type any}, {columnList {18}, type any}, {columnList {19}, type any}, {columnList {20}, type any}, {columnList {21}, type any}, {columnList {22}, type any}, {columnList {23}, type any}, {columnList {24}, type any}, {columnList {25}, type any}, {columnList {26}, type any}, {columnList {27}, type any}, {columnList {28}, type any}, {columnList {29}, type any}, {columnList {30}, type any}, {columnList {31}, type any}, {columnList {32}, type any}, {columnList {33}, type any}, {columnList {34}, type any}, {columnList {35}, type any}, {columnList {36}, type any}, {columnList {37}, type any}, {columnList {38}, type any}, {columnList {39}, type any}, {columnList {40}, type any}, {columnList {41}, type any}, {columnList {42}, type any}, {columnList {43}, type any}, {columnList {44}, type any}, {columnList {45}, type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{columnList {0}, columnList {2}, columnList {3}, columnList {4}, columnList {5}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Particulars"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}, {"Value", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Date"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Particulars]), "Particulars", "Value", List.Max),
    #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

[lbendlin · Accepted solution]

Source = Excel.Workbook(File.Contents(Text.Combine({"H:\My Drive\Work\HFA\HFA - ",Company,".xlsm"})),null, true)

There are a couple of things problematic here:

- File connector - not sustainable. Use a folder connector and then filter by your desired filename

- H: drive data source location.  This will not work in the service unless you use a Personal Gateway that requires your PC to be on when the refresh is scheduled. - not sustainable.  Use a folder in the cloud, ideally a sharepoint/onedrive folder

- .xlsm file  - this may or may not work depending on other factors. Definitely better than .xls or .xlsb, but the preferred extension is .xlsx

[sgupta22]

@lbendlin Thank you very much. This has been extremely helpful and I can't thank you enough.  I finally got the code working and below is the final code that I am using:

let
    Source = Folder.Files("H:\My Drive\Work\Sample"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "HFA - " & Company & ".xlsm")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))), 
    columnList = Table.ColumnNames(#"Expanded Table Column1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{columnList {0}, type any}, {columnList {1}, type any}, {columnList {2}, type text}, {columnList {3}, type any}, {columnList {4}, type any}, {columnList {5}, type any}, {columnList {6}, type date}, {columnList {7}, type any}, {columnList {8}, type any}, {columnList {9}, type any}, {columnList {10}, type any}, {columnList {11}, type any}, {columnList {12}, type any}, {columnList {13}, type any}, {columnList {14}, type any}, {columnList {15}, type any}, {columnList {16}, type any}, {columnList {17}, type any}, {columnList {18}, type any}, {columnList {19}, type any}, {columnList {20}, type any}, {columnList {21}, type any}, {columnList {22}, type any}, {columnList {23}, type any}, {columnList {24}, type any}, {columnList {25}, type any}, {columnList {26}, type any}, {columnList {27}, type any}, {columnList {28}, type any}, {columnList {29}, type any}, {columnList {30}, type any}, {columnList {31}, type any}, {columnList {32}, type any}, {columnList {33}, type any}, {columnList {34}, type any}, {columnList {35}, type any}, {columnList {36}, type any}, {columnList {37}, type any}, {columnList {38}, type any}, {columnList {39}, type any}, {columnList {40}, type any}, {columnList {41}, type any}, {columnList {42}, type any}, {columnList {43}, type any}, {columnList {44}, type any}, {columnList {45}, type any}, {columnList {46}, type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{columnList {0}, columnList {1}, columnList {3}, columnList {4}, columnList {5}, columnList {6}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Particulars"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}, {"Value", Currency.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Date"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Particulars]), "Particulars", "Value", List.Max),
    #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Index", 1, 1, Int64.Type)
in
    #"Added Index"