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.