Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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://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/
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://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/
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |