Replacing values in Power BI revisited

Back in 2022 I wrote a post detailing the various ways you can replace part of a string in M code (Power Query). It's actually the post I refer back to the most, but I've recently come across a couple of related posts so thought I would quickly add them here.

Replace values in a column using if statements

I adapted this one from this thread in the Fabric Forum. In the original example I was trying to remove the same string from any appearance of it in a field in a column, but what about if your problem is the other way around – i.e. the part you want to keep is always the same, but the part you want to lose varies, in unknowable ways.

In this case you can use an if function in the middle of the command like this:

= Table.ReplaceValue(#"Previous Step Name", each [Column1],each 

if Text.Start([Column1]), 7) = "My term" then "My new term" else [Column1],

Replacer.ReplaceValue,{"Column1"})

Replace/Swap multiple values for another value at once 

If you want to replace the entire contents of a cell with another value (by specifying the whole contents in both cases) then you can do use this:

= List.Accumulate({{"YYY", "yyyyy"}, {"XXX", "XXXXX"}},

        #"Expanded Table Column", (state, current) => Table.ReplaceValue(state, current{0},  current{1},  Replacer.ReplaceValue, {"Team"}))

Hacking PowerBI's Funnel Visual

This is a post I wrote specifically to support my entry to the Storytelling With Data's community monthly challenge for June, which is to create a funnel chart.

The real challenge of doing this in PowerBI is that native funnel diagram visual is terrible. Sure, if you have enough slices it just about looks kinda funnelly. But otherwise you just have a bar chart that’s centre aligned and rarely looks anything like a funnel at all (and is ugly to boot).

So I decided to figure out a way to manipulate the native tool into giving you what that manager/client wants, in a way that will dynamically re-adjust as fresh data comes in. That was the trickiest part of this challenge.

Essentially there are three key leaps that got me here: 

1. Creating a calculated table in DAX to get the data in a format that the native funnel tool can use; where…
2. you have sections consisting of many, very thin slices which allows you to get the slope shapes that you want by… 
3. using maths to using make each slice decreasingly smaller between the values required. 

This could be done so you have one giant triangle shape, or if you only had three stages, (as in my case), so it forms the join between the main stages.

1. To make your calculated table you first have to conceptualize it at several smaller tables joined together, so for a 3-stage process you have a rectangular section (A) at the top, followed by a sloping section (B), another rectangular mid-section (C), a further sloping section (D) and finally the stem (E). For each additional stages you need to add 2 extra sections, a sloping section and the section itself.

2. To do this you go back to thinking about those 3 boring long thin lines. For sections A, C & E you're just going to take that length and divide it by a convenient number of slices (I used 25). So you now would have 25 shorter fatter lines of identical length. 

3. The trickiest bit is getting the slopes, but it's not that tricky because you know the length at the top and the bottom (from 2 above). Again you choose your number of slices (I used 15). All you need to do is to find the mid-point (the average length of your lines) and then, the length each will vary by to bridge the gap. If that sounds complex. Don't worry it's figured out below!

Two final points. I also used a measure to dictate the colour of the slice, hence why I have set up the ID column and done it in the way that I have. My colour measure just looks at the first digit and colour odd and even rows differently.

Secondly, even having done this, I was getting feint lines between each stage. To overcome this I just copied and pasted a second version of the diagram and aligned it on top of the original and slightly lower and the lines disappeared. I then grouped them.

The DAX below should work. But if you have any questions just drop me a comment.

Firstly my data looked like this:

For each date and each of the two campaign I'd get the number of calls who we couldn't contact, the number we contacted and who didn't renew and the number we contacted and they did review.

The DAX for the calculated table, then, looks like this:

Funnel Casual =

1. VAR k = 25     // (Main sections - must be odd)
2. VAR j = 15     // (Sloping sections must be odd)
3.  
4. VAR FilteredTable1 = Table
5.  
6. // SECTION A
7. /////////////
8. VAR TotalOutboundCalls = SUMX(FilteredTable1, 'Table'[Calls])
9. VAR LengthValueCalls = TotalTableCalls / k
10. VAR SectionA =
11.     ADDCOLUMNS(
12.         GENERATESERIES(401, 400 + k, 1),
13.         "ID", [Value],
14.         "Length", LengthValueCalls
15.     )
16.  
17. //SECTION C
18. ////////////
19. VAR FilteredTable3 =
20.     FILTER(
21.         FilteredTable1,
22.         'Table'[Contact] = "Yes"
23.     )
24. VAR TotalTableContacts = SUMX(FilteredTable3, 'Table'[Calls])
25. VAR LengthValueContacts = TotalTableContacts / k
26. VAR SectionC =
27.     ADDCOLUMNS(
28.         GENERATESERIES(201, 200 + k, 1),
29.         "ID", [Value],
30.         "Length", LengthValueContacts
31.     )
32.  
33. //SECTION E
34. ////////////
35. VAR FilteredTable5 =
36.     FILTER(
37.         FilteredTable1,
38.         'Table'[Renewal] = "Yes"
39.     )
40. VAR TotalTableRenewals = SUMX(FilteredTable5, 'Table'[Calls])
41. VAR LengthValueRenewals = TotalTableRenewals / k
42. VAR SectionE =
43.     ADDCOLUMNS(
44.         GENERATESERIES(1, k, 1),
45.         "ID", [Value],
46.         "Length", LengthValueRenewals
47.     )
48.  
49. //SECTION B
50. ////////////
51. VAR LengthIncrementB = (LengthValueCalls - LengthValueContacts) / (j - 1)
52. VAR SectionB =
53.     ADDCOLUMNS(
54.         GENERATESERIES(301, 300 + j, 1),
55.         "ID", [Value],
56.         "Length", IF([Value] = 301, LengthValueContacts, LengthValueContacts + (LengthIncrementB * ([Value] - 301)))
57.     )
58.  
59. //SECTION D
60. ////////////
61. VAR LengthIncrementD = (LengthValueContacts - LengthValueRenewals) / (j - 1)
62. VAR SectionD =
63.     ADDCOLUMNS(
64.         GENERATESERIES(101, 100 + j, 1),
65.         "ID", [Value],
66.         "Length", IF([Value] = 101, LengthValueRenewals, LengthValueRenewals + (LengthIncrementD * ([Value] - 101)))
67.     )
68.  
69. RETURN
70. UNION(SectionA, SectionB, SectionC, SectionD, SectionE)
    

If you have a more complicated table, or just one in a different format you can use line 4 to create / summarize / format / filter it as you wish first.

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"[LTTarget 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"[LTTarget 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?