Won't update if Data source not open and save.

[AllanBerces]

Hi good day,

Just want to know, why i need to open and save my data source before my report get update. I recieving csv file from sql every night, i need first to open the file and save, same file same location to save. anything i need to change on my editor.

let
Source = Folder.Files("C:\Users\275427\OneDrive - Aker Solutions\Daily SQL"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, 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"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"WON", type text}, {"Shutdown Mode", type text}, {"Job card", type text}, {"Status No", type text}, {"Project Type", type text}, {"Turnaround", type text}, {"Project Name", type text}, {"Job card description", type text}, {"Task Phase", type text}, {"Estimated hours", Int64.Type}, {"Remaining hours", Int64.Type}, {"Progress last 24 hrs", Int64.Type}, {"Earned last 24 hrs", Int64.Type}, {"Total Earned Hours", Int64.Type}, {"Asset", type text}, {"Vessel", type text}, {"Subcontractor", type text}, {"Actual Stat Date", type date}, {"Week", Int64.Type}, {"Main Cat.", type text}, {"Current Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Main Cat."] = "Q"))
in
#"Filtered Rows"

Thank you

[Sahir_Maharaj · Accepted solution]

Hello @AllanBerces,

Your M query seems fine at first glance, assuming the custom function "Transform File" is correctly defined elsewhere in your query.

Power BI might rely on file metadata, such as the last modified timestamp, to detect changes. If your process of receiving the CSV file doesn't update these metadata details, Power BI might not recognize that the file has been updated.

• Ensure that the process which updates the CSV file also modifies its last modified timestamp. This can usually be achieved with a script that touches the file.
• If manual opening and saving the file trigger an update, you could automate this with a script. For example, a simple PowerShell script could open Excel, load the CSV, and save it, thereby updating its metadata.

Hope this helps.

[lbendlin · Accepted solution]

Don't store your source file on your PC. Store it somewhere where the Power BI Service can access it directly, for example on a OneDrive or SharePoint.

In your case you are using the wrong end of the OneDrive stick.  Change the reference to point to the web end of the stick. Don't reference the file directly, use the SharePoint Folder connector.

[Anonymous · Accepted solution]

Hi, @AllanBerces 

Thanks @lbendlin and @Sahir_Maharaj , I have the following additions. If your query statement is the same every time, you can use Power Query's refresh feature:

With the refresh feature, Power Query re-queries your CSV file for the latest results. If you need an automatic refresh, you should store the files in the OneDrive/SharePoint cloud, as @lbendlin  said. 

Data refresh in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[lbendlin · Accepted solution]

Don't store your source file on your PC. Store it somewhere where the Power BI Service can access it directly, for example on a OneDrive or SharePoint.

In your case you are using the wrong end of the OneDrive stick.  Change the reference to point to the web end of the stick. Don't reference the file directly, use the SharePoint Folder connector.

[Sahir_Maharaj · Accepted solution]

Hello @AllanBerces,

Your M query seems fine at first glance, assuming the custom function "Transform File" is correctly defined elsewhere in your query.

Power BI might rely on file metadata, such as the last modified timestamp, to detect changes. If your process of receiving the CSV file doesn't update these metadata details, Power BI might not recognize that the file has been updated.

• Ensure that the process which updates the CSV file also modifies its last modified timestamp. This can usually be achieved with a script that touches the file.
• If manual opening and saving the file trigger an update, you could automate this with a script. For example, a simple PowerShell script could open Excel, load the CSV, and save it, thereby updating its metadata.

Hope this helps.