cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hymieho
Resolver I
Resolver I

Combine multiple CSV files, then add IsLatest column based on date in filename in Power Query / M

I am importing a series of CSV file and they all have a date in the file name.  Ex:

 

2017-09-26.CSV

2017-10-23.CSV

etc.

 

I am able to combine them and create a date column based on the filename. Now I would like to add a column called [IsLatest, determine which is the most recent date, and have Yes or No in the column accordingly. I can do this in DAX with a custom column, but I wanted to stretch my M skills but I just can't figure it out. Any Power Query / M gurus who can point me in the right direction?

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

Hi @hymieho,

 

You can try a custom column like this:

List.Max(#"Changed Type"[Column1])

Combine_multiple_CSV_files

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Microsoft
Microsoft

Hi @hymieho,

 

You can try a custom column like this:

List.Max(#"Changed Type"[Column1])

Combine_multiple_CSV_files

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Works like a charm--thanks for the tip!

 

1. Add a (temporary) custom column as you mentioned with the List.Max(date)

2. Add a 2nd custom column that checks to see if [Date] = [Latest Date in group]

3. Remove the temp column

 

#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "IsLatest", each if [Add Column with latest date] = [Date] then "Yes" else "No"),
#"Renamed Columns2" = Table.RenameColumns(#"Added Conditional Column",{{"Add Column with latest date", "Latest Date in group"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns2",{"Latest Date in group"})

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.