pinarello
Member
- Joined
- Jun 21, 2019
- Messages
- 218
- Reaction score
- 4
- Points
- 18
- Location
- Germany
- Excel Version(s)
- Office 365
Question posed in the german ms-office-forum.de:
In a list with many timestamps (Input worksheet), there can be one or more IDs for each timestamp. Now the questioner wants to know which ID was booked first in a list of manually entered timestamps. But if the ID "FM0" exists for a timestamp, then this ID should be reported as the result.
With a formula combination of COUNTIFS and VLOOKUP, it is no problem to get the correct result. But since the questioner asked for a VBA solution (many users don't know that very often Power Query can be used as well), I also developed a solution with Power Query.
For all timestamps only the first ID or, if available, the FM0 ID is determined and only this one is loaded as connection. In the evaluation query the ID of the timestamps is determined by merging them. The Merge window also shows the correct IDs. But as soon as I expand the result of the merge in the next step, the FM0 IDs are no longer shown.
I have tested my PQ query several times and cannot find any errors in it. Therefore I think this must be an error in Power Query.
Some weeks ago I already reported a bug: https://www.excelguru.ca/forums/sho...rt-Converter-deletes-line-feeds-automatically
In a list with many timestamps (Input worksheet), there can be one or more IDs for each timestamp. Now the questioner wants to know which ID was booked first in a list of manually entered timestamps. But if the ID "FM0" exists for a timestamp, then this ID should be reported as the result.
With a formula combination of COUNTIFS and VLOOKUP, it is no problem to get the correct result. But since the questioner asked for a VBA solution (many users don't know that very often Power Query can be used as well), I also developed a solution with Power Query.
For all timestamps only the first ID or, if available, the FM0 ID is determined and only this one is loaded as connection. In the evaluation query the ID of the timestamps is determined by merging them. The Merge window also shows the correct IDs. But as soon as I expand the result of the merge in the next step, the FM0 IDs are no longer shown.
I have tested my PQ query several times and cannot find any errors in it. Therefore I think this must be an error in Power Query.
Some weeks ago I already reported a bug: https://www.excelguru.ca/forums/sho...rt-Converter-deletes-line-feeds-automatically
Attachments
Last edited: