Forum Discussion
Importing multiple files from folders basis on current year vs last year
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.
Super UserJaycena2024 , 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 year
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.