Forum Discussion
Helper IIHow to Dynamically Enter Date for Subset of Data each Data Refresh
You cannot do directly what you are asking here matsahiro. When Excel processes a macro based on how I am understanding what youa re going, it is entering a fixed amount based on that formula and it will not change. In other words, it is keying the correct date for you over the dataset for all new data, correct?
In Power BI, any formula in a custom column is recalculated each time the query is refreshed and runs over the entire dataset - it effectively deletes all data in the model then fully reloads it again. If you need the data timestamped it will have to be done at the source.
If that is not correct, please clarify exactly what is happening and what you expect to happen. If you just want a date based on a date in your table, you can use the formula mahoneypat provided, or you can replace his [SaleDate] with DateTime.Date(DateTime.LocalNow()) which is Power Query's version of Today(). But again, it will do it on the entire data set. It will not leave yesterday's data untouched. Power BI isn't built to modify and incrementally add and modify records. It does it on the entire dataset.
If you need it processed the way you want, I would suggest a few alternatives:
- Power Automate could to it for you depending on the dataset. If it is in an Excel file, it would need to be formatted a particular way for that table.
- Power Apps could also do it.
- Continue running your Excel macro daily (or whenever) to modify the data before Power BI reads it in.
Helper IIIf I choose to do this method in Power Apps, how would I go about that? I do not have any experience with Power Apps and would greatly appreciate any tips. Thank you very much.
Community ChampionHi matsahiro - The data would have to be in a table format. It really gets beyond the scope if this forum on how that would work. Your best bet is to search for some articles or videos on using Power Apps with Excel files and ask the excellent helpers at the Power Apps community.
In a simlar vein, Power Automate might be better here. It could run on a schedule or when the file is modified and update the data for you. Again, it must be in a table, and the Power Automate Community will be the best resource for you there.
If I were doing it, I think I would start with Power Automate first. I don't think either solution is wrong here though.
FWIW, this Excel Online Action is what I think would work here, but I am no Power Automate expert, so the forum links are the best bet still.:
- matsahiro
Really appreciate the resources and help!