Forum Discussion
Importing multiple files from folders basis on current year vs last year
I have folders in the google drive named on a yearly basis from 2019 to 2024. Every year has folders in months (Jan - Dec) and every month folders has csv files (~7mb in size) date wise.
I want to build a logic to import two csv files (to do analysis on current year vs last year performance)
1. Current Year latest date file i.e, 19 Mar 2024
2. Last Year date file i.e., 19 Mar 2023 or which ever is closer (assuming cases like 19 Mar 2023 is a holiday etc)
Can you help me in building this logic, I have imported the root folder in PBI to select all the files, Now I want to select these two files to further build the dashboard.
The path of the folder is like G:\Shared drives\…\2024
and the logic I have used to select the current year latest file is
Table.SelectRows(#”Filtered Rows”, let latest = List.Max(#Filtered Rows”[Date created] in each [Date created] = latest)
Hi Jaycena2024 ,
Firstly I think you need to convert these file names into a date format that Power BI can recognize. This will allow you to perform date-based operations on them.
Once you have the dates in a recognizable format, you can use Power BI's query editor to filter for the two specific dates you're interested in. You can use custom M code in the Advanced Editor to dynamically calculate the dates for "today" and the same day in the previous year. Here's a simplified example of how you might start to approach this:
let Today = DateTime.LocalNow(), LastYear = Date.AddYears(Today, -1), FilteredRows = Table.SelectRows(YourTableName, each [YourDateColumn] = Today or [YourDateColumn] = LastYear) in FilteredRowsAlso you need to ensure that your Power BI report always selects the current date and its counterpart from the previous year without manual intervention, you'll want to incorporate dynamic date calculations. The M code snippet above demonstrates a basic approach to this by using DateTime.LocalNow() to always use the current date as part of the filter criteria.
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.
3 Replies
- amitchandak
Super User
Jaycena2024 , Have one more variable
_last = Date.AddYears(latest,-1)
_lastmax = List.Max(List.Select("Last Step Table"[Date], each _ <= _last))YourstepModified=
Table.SelectRows(#”Filtered Rows”, let latest = List.Max(#Filtered Rows”[Date created] in each [Date created] = latest or [Date created] = _lastmax))
Thanks amitchandak for replying
So I have imported the folder in step 1 (image below)
and then filtered the rows, having only csv files in step 2
Now can you tell me where can I add the steps for new variables to select the latest csv files (i.e., 19 mar 2024) and then it's counterpart from last yearHi Jaycena2024 ,
Firstly I think you need to convert these file names into a date format that Power BI can recognize. This will allow you to perform date-based operations on them.
Once you have the dates in a recognizable format, you can use Power BI's query editor to filter for the two specific dates you're interested in. You can use custom M code in the Advanced Editor to dynamically calculate the dates for "today" and the same day in the previous year. Here's a simplified example of how you might start to approach this:
let Today = DateTime.LocalNow(), LastYear = Date.AddYears(Today, -1), FilteredRows = Table.SelectRows(YourTableName, each [YourDateColumn] = Today or [YourDateColumn] = LastYear) in FilteredRowsAlso you need to ensure that your Power BI report always selects the current date and its counterpart from the previous year without manual intervention, you'll want to incorporate dynamic date calculations. The M code snippet above demonstrates a basic approach to this by using DateTime.LocalNow() to always use the current date as part of the filter criteria.
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.
