PDA

View Full Version : Power Query: find which column contains my value and return column name



kerygma
2016-07-11, 06:33 PM
I have 70 columns with dates, and one column with the latest date in it. I want to return the name of the column with the latest date. Any ideas? I can do this by loading to a spreadsheet and using formulas (max, index(match), but then can't get that data back into a query since it's in the query report spreadsheet.)

Thanks in advance!

Brian Smith
2016-07-11, 06:48 PM
Could you unpivot the date column sort the results and drill down as a value to the first one then it could be used in any of your queries.

kerygma
2016-07-11, 08:06 PM
[edited]... oops. Sorry Brian, thank you for your reply. I had this screen open but I guess it logged me out and I didn't even see your response until I posted this. Thank you! I'm not sure the best way to be notified of responses to my posts on the forum, but you sure were fast!

[original post]
SOLVED...Welp. After a lengthy two query process... I figured out that unpivoting, sorting, buffering, and removing duplicates did the trick. Man I love power query, but it definitely takes some creative thinking!