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. 

Sum totals for Columns redux

Back October I showed how to run a quick sum of a column in Power Query. But I curious to know how I could see this total alongside a count of the number of rows. It turns out this is quite easy because you can include text in your output from a Power Query script. In fact you can even combine these two totals with an explanation as I have done above.

A few points to remember in doing this:
1. If you're creating a variable* in Power Query (i.e. a line in the M code) you don't have to call it right away you can call on it in the final "in" line.

2. You can't minx numbers with text unless you convert them to text first.

So the only code you need really (on top of what you many have already done) is:

#"Counted Rows" = Table.RowCount(#"PreviousRow"),
Total = List.Sum(#"Filtered Rows1"[Users])

in

"Total= "&Text.From(Total)&" from "&Text.From(#"Counted Rows")&" rows"

Add extra rows in Power Query dependent on value in cell/column

If you're working in Power Query, and you need to add a whole bunch of blank rows, or rows based on the value from another column. How do you do it?

I went round the houses with this one. There are two key pieces to the puzzle. The first is relatively easy/well known. If you split columns by delimiter you can (under advanced options) do this into new rows rather than new columns.

But how to get from the number in the cell/column to something with which split columns by delimiter could work?

At first I tried putting the cell (actually in my case it was a subtracting one cell from another) as a power of ten, but I couldn't work out how to get it out of 1e+48 mode, to then convert to text and delimit. 

Then I came across Catalin Bombea's post on this forum which I modified. If you add a new column using this formula:

= Table.AddColumn(Source, "NewColumnName", each Text.Repeat("¬",[ValueColumn]-1))

This will give you a string of ¬s as long as the value in the cell/column, for each row in the table (even if it's just 1. 

You then just use split by delimiter and away you go. Only tried it with Power BI but it should work with Excel too.

How do you sum a total for a column in Power Query?

Power Query has a "Count Rows" button, but there's no equivalent button for when you want to quickly get the total of your values column, to see total sales or total users etc.

But there's good news! There are two relatively simple ways to do this. The one-step version for those happy to work in Advanced Editor, and the easier two-step version for those who aren't. Let's take them in reverse order.

The easier, two-step, version
1. Choose "Add Column">"Custom Column" and in the formula box just type "Total" (with quotes)
2. Now chose "Transform">"Group By". In the 1st box select your new column, under "Operation" choose "Sum" and under "Column" choose the column that has the values to be summed. Press OK to get the above! 

The one-step Advanced Editor version
In a way this is even simpler. Just go to Advanced Editor, put a column on the end of the last line of code and press "Enter". On the new line type Total = List.Sum(Source[Users]). Change the very last line from Source to Total and press OK.

Hat tip to John Dalesandro's post Microsoft Power Query for Excel Tips and Tricks, which gave me enough to work the rest out. Should work for Power Query in both Power BI and Excel.

Directives vs Commands in AHK/AutoHotKey

I couldn't find a direct answer to this question, so thought, once I'd managed to piece it together I'd post it here for any future searchers.

In general programming a command is a specific type of directive which tells a programme to do a particular task. However, AHK plays by its own rules. 

According to Frankie Bagnardi, in AHK "Directives are processed just before the script starts and allow you to customize AutoHotkey’s behaviour". There are two types. The first is just the bits of code at the top of a script, but they are also anything that starts with a #.

So in AHK directives are the bits of code starting with a # (hashtag/pound sign in the US)

So if you use, say, #IfWinActive partway down your code, the the commands that follow it will be specific to that context, until you turn it off (by using the same directive, with no conditions/parameters afterwards).

Directives are a type of commands which use a # to create a context. Bits of code that aren't functions, expressions, variables, parameters, text etc. are all commands, (e.g. Send, Sleep, Run, MsgBox). Directives are a specific subset of the available commands.

And, for the sake of completion, functions are those bits of code that come with brackets(parentheses). Expressions are bits of maths.

- If I've got this wrong, please let me know in the comments without shouting or calling me an idiot...

iPad Native Keyboard Shortcuts

I've had an iPad for over a decade and always found it's keyboard, particularly the lack of backwards and forwards keys, frustrating. Recently however I got a new model and, quite by accident I discovered a useful shortcut that when I Googled, no-one else seems to have picked up on (or all the people who have are buried below the usual pile of SEO-d crap).

Anyway if you click the "I" key and then the "O" key in quick succession it selects the word the cursor is on or after. 

IO, just like like that!

In other words I-O (I+O?) is like double clicking with a mouse. It's a minor thing but gets me wondering what other hidden combos there are.

I set off trying to find out and then discovered that Apple have, in fact, done something closer to left and right arrow buttons. If you press and hold the space bar the markings blank out and you can then use the keyboard as a track pack. I got that thanks to this video by Steve Katz.

Does anyone know of any others?

How to get an editable list of all the files in a folder from Windows Explorer

Often you have a lot of files in a folder and for some reason you want to paste them in Word, Excel, Notepad or something else, but a straight select all, copy & paste doesn't work. While solutions exist for that online using the registry editor, they sound a bit foreboding or complicated, or your IT won't et you have permission. 

Here's an easier way: Copy the folder path from the bar in Windows Explorer (e.g. C:/Users/your.name/Documents) and drop it in to the URL bar in MS Edge or Chrome. Et voila! You can then copy and paste the text wherever you want.

If this doesn't work either check you've used "/"s and not "\"s. Secondly try using a different folder and navigating your way there through the folders in Chrome/Edge. 

How to Differentiate Between Similar Field Entries

Say you run a series of events and want to display registration, attendance etc on a bar chart in Power BI. The length of the event names and limitations on the Power BI naming fields in bar charts mean that you can't use the name on the Y-axis, and date gives you problems as well. So you go for a variation on the date worked out in a calculated field during the Power Query part of the process.

This is all fine until the day you run two events on the same day. Now Power BI thinks this is just one event. So you need to find someway of telling the difference between the two events, preferably with a reference to the date of the event.

A way to do this is to add a further calculated column using DAX. This will append an a & b (etc.)  onto the date field when there's a clash, but be blank otherwise. Here's the DAX to do that where 

St.Date =

VAR distinctlycounting = COUNTROWS(FILTER(Titles,Titles[Date]=EARLIER(Titles[Date])))

VAR ReIndex = CALCULATE(DISTINCTCOUNT(Titles[Title]),FILTER(Titles,

Titles[Date]=EARLIER(Titles[Date])&&

Titles[Title]<=EARLIER(Titles[Title])))

RETURN IF(distinctlycounting = 1,Titles[Start Date],SWITCH(ReIndex,

1, [Start Date] & "a",

2, [Start Date] & "b",

3, [Start Date] & "c",

4, [Start Date] & "d",

5, [Start Date] & "e",

6, [Start Date] & "f",

7, [Start Date] & "g",

8, [Start Date] & "h"

))

Where the table is called "Titles", the date field is called "Date" and "Start Date" is the calculated field in Power Query.

Obviously you can add more letter variations if you have more events in a single day, or do away with that bit altogether if you just want to use numbers (and you can also get rid of the CONVERT function wrap around as well.