Vlookups in Power Query (for power BI)

If you're familiar with Excel before moving to Power Query then it's a little maddening to find that PowerQuery doesn't have a built in "Vlookup" option. It's easily done via the M code though, you simply add this code:

Whole thing:
= Table.AddColumn(#"Previous Step", "Name of New Column", each #"Lookup Table"[Target Field]{List.PositionOf(#"Lookup Table"[LTMatching Field], [Matching Field])},Text.Type)

Where the "Target Field" is the one you want to get the value from using the "Matching field" in the current table which corresponds to the "LTMatching Field" in the other table.

Add a Column Box

Alternatively you can just do this through the add a column box where you'd just put:

#"Lookup Table"[Target Field]{List.PositionOf(#"Lookup Table"[LTMatching Field], [Matching Field])}

Usually though it's better just to merge the two queries. I do suspect this method might be a bit quicker though.

The new Twitter / 𝕏 logo as white png

↑ This is a temporary one and not designed to be viewed large as the lines are a bit wobbly. But I couldn't find a better one out there that was square rather than rectangular (and downloadable).
↓ Here is one with a border.
Hope these are useful.

Formula for converting US dates to UK in Excel

If you've ever imported data from a US supplier into a (UK) spreadsheet you may have found that the dates and times have gone wrong in Excel. This can happen in various ways but a difficult form of this problem sees the first twelve days of the month characterised back to front (i.e. days marked as month and vice versa) whereas those afterwards are just marked as weird text. IN this scenario the time is included and serves to complicate things further

There are various ways of solving this, although some only really work if you encounter this at the start of using the sheet, but none are particularly simple to implement.

One way – and this should work at any stage and if your dates are mixed up – is to use a formula. Here's one I've written using the ground-breaking new "Let" function which essentially lets you use variables. So if the first dodgy date is in A2 you type:

=LET(stringx,IFERROR(TEXT(ROUNDDOWN(A2,0),"dd/MM/yyyy"),TRIM(LEFT(A2,10))),

           firstslash, FIND("/",stringx),

           monthx, LEFT(stringx,firstslash-1),

           dayx, MID(stringx,firstslash+1,FIND("/",stringx,firstslash+1)-1-firstslash),

           yearx, RIGHT(stringx,4),

DATEVALUE(dayx &"-"& monthx &"-"& yearx))

What I've done here is create 5 variables. The first converts both types of date (pre and post 12th of month) into text and saves that as a string, stringx. The next finds the location of the one for the position of the first slash and then the last 3 use those two to create new strings/variables for day, month and year. Finally you plug those into a DATEVALUE function et voila.