Use Row value to match column name and take value from column along same row in PowerBI

Question

This is all through Power Query on PowerBI.

I currently have a table that looks something like this:

Number	Vehicle	Tractor	Car	Lorry
317968	Car	27/03/2022	21/03/2023	21/03/2023
312388	Lorry	21/01/2024	16/08/2021	11/07/2022
316671	Car	18/09/2022	24/12/2023	18/09/2023
318328	Tractor	12/01/2024	03/03/2023	21/08/2024
328543	Tractor	19/08/2022	15/11/2022	21/03/2023

I am looking at creating a new column which only takes the date from the column that matches the Vehicle column returning something like this:

Number	Vehicle	Tractor	Car	Lorry	New Column
317968	Car	27/03/2022	21/03/2023	21/03/2023	21/03/2023
312388	Lorry	21/01/2024	16/08/2021	11/07/2022	11/07/2022
316671	Car	18/09/2022	24/12/2023	18/09/2023	24/12/2023
318328	Tractor	12/01/2024	03/03/2023	21/08/2024	12/01/2024
328543	Tractor	19/08/2022	15/11/2022	21/03/2023	19/08/2022

Answer 1

You can reference a column by name, using the Record.Field function:

= Table.AddColumn(#"Previous Step", "New Column", each Record.Field(_,[Vehicle]))