Can you Extract Information from the Column Title?

Question

Hello!&nbsp;First time poster. While I am new to BI, I have some years working Tableau and other programs. If I can provide any more info, please let me know! I'd be happy to.&nbsp;&nbsp;I have been given the glorious task of auditing the "Total Paid" column from one of my companies Excel Workbooks and verifying that the amount is accurate compared to the [date] Amount Paid columns as shown in a manufactured table below. The total paid column is adjusted monthly by hand by adding the current month's payment to the existing total (I2 + K2 + M2 + ...). This means that my formula needs to stop at today's date and then anything in the workbook that is future dated needs to return 0.&nbsp;&nbsp;I'm fairly certain that if this is possible, my issue lies with calling the column title. I've offered one of my many attempts in the code snippit below, but it's simply incorrect and I finally realized it's my attempt at calling the COLUMN statement. Is there possibly a function or some other way that can provide this information to me? I need the first ten characters of the column title and then convert that into a date to compare it to today's date.&nbsp;&nbsp;As this will be used on a live file, I am extremely limited in what I can do or change in the original table in this less than ideal file. However, if this is impossible and I will need to ask for a modification to make this work, please let me know. One suggestion I have considered is asking them to modify the table and assign the individual dates to row 2 (row 1 after the headers have been claimed by the table import). Then just call that row's information in the same column and compare against that.&nbsp;&nbsp;Thank you so much for your time!&nbsp;The code box did not work so I am posting it manually -&nbsp;AmountPaidWithDateCheckV5 = VAR TodayDate = TODAY() RETURNIF(ISBLANK('Table Title'[09/01/2023 Amount Paid]), 0, IF(TodayDate &gt; DATEVALUE(LEFT(COLUMN('Table Title'), 10)), 'Table Title'[09/01/2023 Amount Paid], 0)) +IF(ISBLANK('Table Title'[10/01/2023 Amount Paid]), 0, IF(TodayDate &gt; DATEVALUE(LEFT(COLUMN('Table Title'), 10)), 'Table Title'[10/01/2023 Amount Paid], 0)) +\Repeat for all needed columns in the table&nbsp;Contract #Total ObligationTotal PaidTotal Remaining1st payment due date09/01/2023 Rent Payment09/01/2023 Amount Paid10/01/2023 Rent Payment10/01/2023 Amount Paid1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 70,000.008/1/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.003456&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 54,000.009/1/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,000.005678&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,500,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200,000.0010/1/2023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300,000.007890&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000.0011/1/2023&nbsp;&nbsp;&nbsp;&nbsp;

dataninja777 · Accepted Answer

Hi&nbsp;JacobR&nbsp;
My initial impression regarding the best way of tackling of your glorious task&nbsp;😅 is to use Power Query's unpivot other column function to convert your wide table into long narrow fact table which will be more easily digested by power BI formula engine, dax.&nbsp; After clicking operation using Power Query, you can quickly restructure your wide table into something like below without knowing M codes.&nbsp; As long as your wide table is maintained in the consistent manner by your manual source provider in the future, you can just right click and refresh to get the more data-analysis friendly table like below without repeating the Power Query clicking operation you do in the first instance when creating the table below.&nbsp;
&nbsp;

I attach an example pbix file, even though the steps (clicks) taken may contain some redundant actions and can be improved.&nbsp;
&nbsp;

Forum Discussion

Can you Extract Information from the Column Title?

2 Replies

Related content

Extracting complicated text from a column

Extract Data from sharepoint

Extract 2 digits from a column

Rest API Informations

Extract Cetrain Characters from Text Column

Contract #	Total Obligation	Total Paid	Total Remaining	1st payment due date	09/01/2023 Rent Payment	09/01/2023 Amount Paid	10/01/2023 Rent Payment	10/01/2023 Amount Paid
1234	100,000.00	30,000.00	70,000.00	8/1/2023	10,000.00	10,000.00	10,000.00	10,000.00
3456	60,000.00	6,000.00	54,000.00	9/1/2023	3,000.00	3,000.00	3,000.00	3,000.00
5678	1,500,000.00	300,000.00	1,200,000.00	10/1/2023			300,000.00	300,000.00
7890	10,000.00	-	10,000.00	11/1/2023