Dashboard Refresh with new files with month name

[adhumal2]

Dear Experts,

I am creating a dashboard wherein I need below support

- How can I define the path of the folder from where the dashboard will automatically pick the data? Whenever I add a file in folder and refresh the database, the dashboard should  get updated.

- The dashboard should pick the month name and year from Excel file name which i add into the folder 

Is there any link to any document/article/video in which this has been explained in easy terms?

Many Thanks in advance.!

[mahoneypat · Accepted solution]

Please see this video on how to combine files from a folder.  You are able to keep the filename, and if you've named them consistently (e.g., Month_YYYY), you can parse the file name to create a date column if needed.  For refresh, you can set up scheduled refresh from the Power BI service, or you can set up a Flow to refresh your dataset when a file is added (if the frequency of that is less than your refresh limit).

https://www.youtube.com/watch?v=9sfCDCpWTfc

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-diye-msft · Accepted solution]

Hi @adhumal2 

Kindly refer to documents:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries 

https://powerbi.tips/2016/06/loading-data-from-folder/ 

More reference:

https://medium.com/@wkrzywiec/passing-source-folder-path-as-parameter-to-query-code-in-power-query-1... 

https://powerbi.tips/2016/08/using-variables-for-file-locations/ 

If you published the pbix into service and manage the gateway. the file would be refreshed on-demand/scheduled. kindly refer to:

https://powerbi.microsoft.com/en-us/blog/refresh-for-on-premises-sources-is-here/ 

[mahoneypat · Accepted solution]

Please see this video on how to combine files from a folder.  You are able to keep the filename, and if you've named them consistently (e.g., Month_YYYY), you can parse the file name to create a date column if needed.  For refresh, you can set up scheduled refresh from the Power BI service, or you can set up a Flow to refresh your dataset when a file is added (if the frequency of that is less than your refresh limit).

https://www.youtube.com/watch?v=9sfCDCpWTfc

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[adhumal2]

@mahoneypat@v-diye-msft  Hi Pat and V-diye, Many Thanks!

How can I parse the month name and year and create the column in the query for Month Name/Year or Date?

I have file names like 

 - EPD Report - Mar 2020

- EPD Report - Jan 2019

Your help is highly appreciated.

[mahoneypat]

Here is one way to convert those filenames to dates.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0lVwDXBRCEotyC8qUdBV8E0sUjAyMDJQitWJVgITaAq8EvOACgwtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filename = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Filename", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Filename] <> "")),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Filename", each Text.AfterDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Filename", Text.Trim, type text}}),
#"Added Custom" = Table.AddColumn(#"Trimmed Text", "DateFromFilename", each "01-"&Text.BeforeDelimiter([Filename]," ")&"-"&Text.AfterDelimiter([Filename]," ")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateFromFilename", type date}})
in
#"Changed Type1"

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat