Handling Weekly data files and visualizing history

[drallam]

HI,

I have data sets (excel Files) from each week and a new file is generated every week. how do i visualize that data and be dynamic?

Any ideas?

[v-ljerr-msft · Accepted solution]

Hi @drallam,

---

I have data sets (excel Files) from each week and a new file is generated every week. how do i visualize that data and be dynamic?

---

As @Phil_Seamark has mentioned above, using Folder connector could be a good choice here in your scenario. For more details about how to import data from a folder in Power BI Desktop, you can refer to this article. 

Excatly. I;m trying to assign the dates in another column. you replied to that post too. 

---

Based on my test, you should be able to follow steps below to get your expected result. 

1. Use the formula below to add a custom column under Add Column tab in Query Editor.

=try Date.FromText([Column B]) otherwise null

2. Use the Fill Down option to fill the null value with the corresponding date in the column.

Regards

[drallam]

Thanks for the solution. I was able to do this at 1 in the night lol. BUt I used a caveman's formula, instead of the nifty date from text.

= Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Employee ID],"AM") then [Employee ID] else "")

Then used fill down just like you did. 🙂 

[Phil_Seamark]

Hi @drallam

Power BI can import multiple files in the one folder including excel files and sew all the data together to be a single table.  Use the Query editor for this and make sure you deal with duplicates.

So long as you have a date column, you can aggregate this to weekly data and plot on any number of the visuals in Power BI.

[drallam]

Excatly. I;m trying to assign the dates in another column. you replied to that post too. 🙂

[drallam]

Excatly, I'm trying to get the date column. You replied to that post too