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. 

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.

Get a List of Excel Tab Names and Compare Headers on Multiple Tabs

Get a List of Excel Tab Names
1. Go to Formulas > Define Name
2. Then in the New Name box, put Tabs in to the "Name" box
3. In "Refers to" paste in this formula =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","") & click OK

4. Paste this formula into cell A1 =INDEX(Tabs,ROWS($A$1:$A1))
5. From the bottom right corner of A1, drag the black square for as many cells as there are tabs

This should give you a nice long list of of your tab names.
You can then use this to allow you to compare headings


Compare Headers on Multiple Tabs
If you have a list of Tab headings in Col A then to get a compare list do as follows:

a. In cell B1 type this formula ="¬'"&$A1&"'!B2"
b. Drag that down to your last active row
c. Copy and paste-as-text into column C
d. Make sure that Number Format is marked as "Text"
e. Now select that column and do a find a replace, swapping ¬ for =.
f. Now change the number format to "General" - This should give you a list of the first header on each sheet.
g. Now with all active cells in col C selected, drag right as far as you need.
h. You should now see all your headers and can see how they compare to each other.

Note - if you make changes in the cells of the original headers these should be reflected, but if you delete or move sheets these changes will not reflect and might make things worse.