Forum Discussion
Adding a Date Column to an existing Table that references date of source
I have a series of tables in my power BI file (a template i inherited from another person). Most of the tables have had a Date column that has been added to them, so that you can drill down the data on the table to look specifically at data from a selected date.
One of the tables I recently added through a new query, does not have this date table, and my attempts to copy the "invoke custom function" steps, to match what the other tables have, has not gone well...
This seems like it should be straight-forwared, but i cannot figure it out. Any help you all can offer would be greatly appreciated.
Here is the table:
Here is the Source File info
And what I want to do it pull the "Date Modified" info from the source files, and include them in a Date Column in the main table.
Hello,
You need to extract the values left of the "_" Delimiter. This should leave you with an 8 digit number 20240610, etc.
From here there are two ways to get the date. One through Power Query M in transform data, or DAX (my preferred way). For the DAX you can use a combo of LEFT, MID, and RIGHT respectively to get your YEAR, MONTH, and DAY.
3 Replies
ExcelMonkeImpactful Individual
Hello,
You need to extract the values left of the "_" Delimiter. This should leave you with an 8 digit number 20240610, etc.
From here there are two ways to get the date. One through Power Query M in transform data, or DAX (my preferred way). For the DAX you can use a combo of LEFT, MID, and RIGHT respectively to get your YEAR, MONTH, and DAY.Thank you. This worked.
I will mention that the other tables that had the date added did not do it this way, so I was skeptical that the result would work for what I needed (a global date selector on the front page, based on the date modified data of the Excel files) - but it did!!!! So thank you. I can't believe how much time I spent trying to do this some weird Invoke function way....
- ExcelMonke
Great! Glad that helped resolve your problem 🤠Please mark my response as a solution so that other users may find it helpful too