Forum Discussion
Building trends from multiple files
hello. thanks in advance for helping me. everyone here is always very helpful and quick to respond. I think this is going to be an easy one.
I dump a daily report to a folder for pBI to trend. The file name is consistent with the date at the end of the name. For my example, the file names are salesdatayyyymmdd.csv. I dont need any help setting up trending. Im doing that elsewhere in reports and its working wonderfully....
My question is.... i have daily dump of consistently named files....
I want to pull the latest numbers from a table... but i also want to build a column that pulls yesterdays numbers, another column that pulls "last week's" numbers, and a column that pulls "last month's" numbers... in such a way that when i refresh every day, the numbers still follow "today", "yesterday", "last week", and "last month"...
Below are some sample tables with filenames:
Im sure there is some pretty simple DAX or M code that can dynamically pull todays date, todays date -1, todays date -7, and todays date -30? but skipping the weekends... my powerBI report would looks somethng like this:
Youre awesome! thanks in advance!
6 Replies
In Power Query use the folder connector. Ingest all the files (make sure to keep the file name around). Then in Power BI create your report as needed.
Thanks for your reply. I am looking for a much more advanced answer though. I am using the folder option for quite a number of tables to build trending... but Im looking for specific coding examples of how to pull column data from multiple different files in a folder to show a table of data, historically. A dynamic column for "today", "yesterday", "last week", and "last month" using the rolling files for those dynamic days.
Provide sample data, and I can show you sample Power Query code.
Hi Anonymous , Did you ever find a solution for this? I have a similar sceniro.
