Text Contains in an IF Statement

Power Query
1

I need to put the decimal value 10 in a field on a row if the person’s name is John and they are older than 50.

I take it I need to use Text.Contains - but hj how doe I do this in an IF command

Thanks

Allister

Book13.xlsx (9.6 KB)

2

@AllisterB,

Give this a go:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUQrIzEtVCC4BskwtlGJ1opWCE3NyKhWcivLL84CCzk4uQNLIQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Address = _t, Age = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Address", type text}, {"Age", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains( [Name], "John" ) = false then null else if [Age] > 50 then 10 else null)
in
    #"Added Custom"

image
image1024×132 6.85 KB

3

Hi Brian

Could you send your file in xlsx format - thank You

Allsiter

4

@AllisterB,

To my knowledge, there’s no way to convert a PBIX file to XLSX. Monkey Tools has import of Power BI data model into Excel in its future roadmap, but this feature has not yet been implemented.

  • Brian
5

The Excel version does not allow for this type of nested LET statements (it might allow others, but this one did not want to work)

So, I had to make a new Query for this portion of the query

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Address = _t, Age = _t]),

The attached xlsx file has Table1 (loaded from your example @AllisterB, an the _t table that does the magic

image

The solution is still the one presented by @BrianJ :slight_smile: Book13.xlsx (150.7 KB)

1 Like
6

@Heather,

Thanks very much.

It’s the weirdest thing - I’ve used Excel since the late 1980s and at one point definitely would have been considered an expert (literally - I built complex Excel models that I testified to in Federal court as an expert witness). But if you were to watch me with it today, you would think I was a complete noob. Other than occasional forays into Analyze in Excel to debug a complex PBI measure, all I use Excel for these days is a place to connect Get Data to in Power BI. It’s like going back to visit an old neighborhood that you barely recognize anymore…

  • Brian
1 Like
7

No worries, I still have to visit that old neighborhood, and work in it frequently - in fact, I’m facilitating some Excel training tomorrow afternoon for our Sales team.

As for PowerBi, the first trainer I worked with taught everything in both Excel and the Desktop. At first, I only wanted PowerBi for the ETL capabilities, the DAX was a major frustration. In fact, because I didn’t have a version of Excel that could use the Power Query addin, I was doing things in Desktop and then copying it back into Excel! :laughing:

8

Hi @AllisterB, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

9

Hi @AllisterB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!