This is a great use case for the “Add Column from Example” feature of PowerQuery. Sam did a great video on it not long ago.
Column From Examples - My Favorite Feature Within The Power Query - YouTube
The end result will likely be the same code that @Melissa provided, but it’s just a different way of getting there.