Date from Text String

[Qqqqq]

I'm brand new to Power Query, and I need to add a date column to my data. The tables that my data is built from are named with the last 6 digits representing the year and month. From the table name, I would like to get the last date of the month. For example, the September 2020 data is in a table named t_202009, and I would like 9/30/2020 in a new date column.

I tried to use "Add Column From Examples...", but it seemed to require that I give it a new example for every table. This isn't practical, as I want this column to auto populate as new tables are added for future months.

What is the best way to get this result?

 

[sandy666]

use XL2BB to post representative example of your source data and expected result or post a link to the shared excel file with the same via onedrive, googledrive or any similar service

 

[Qqqqq]

My source data has confidential information, so I cannot post it. The data itself has no date. Tables use the naming convention t_YYYYMM. From the table name, I would like to create a date column, assigning the last date of each month to the data entries for that month.

 

[sandy666]

confidential information

this is not a problem, you can use generic data and post a link to the shared excel file with a proper table names

 

[Qqqqq]

I think I've got it figured out. This formula seems to have done the trick.

= Table.AddColumn(#"Filtered Rows2", "Date", each Date.EndOfMonth(Date.From(Text.AfterDelimiter([Name]&"01", "_"))), type date)

 

[sandy666]

Glad you found a solution
Have a nice day