Rept function in Power Query?

S

sarmyles

New member
Joined
May 8, 2016
Messages
2
Reaction score
0
Points
0
In Excel I use the function
=REPT("Buprenorphine",ISERROR(SEARCH("Bupr",AT14087))=FALSE) to search column[Additives] for text Buprenorphine

- ISERROR(SEARCH("Bupr",AT14087))=FALSE - evaluates to TRUE. The Rept () function accepts TRUE as being equivalent to 1

In Power Query I have tried

Text.Repeat("Buprenorphine(Text.Contains([additives],"Bupr"))

but this returns an error. If I use

Text.Contains([additives],"Bupr")

this evaluates to true when Buprenorphine is found in [Additives] but the second parameter - repeat count - of Text.Repeat does not seem to accept TRUE as equal to one which occurs in Excel.

How can I convert TRUE to 1 ? Thanks Steve

([additives] is formatted as text)
 
Hi Steve,

you can format the TRUE / FALSE column as integer. This you can do from the normal menu.

Or try out this by copying the code into the advanced editor in an empty query (Quelle = Source):

let
Quelle = {1,2},
#"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"In Tabelle konvertiert", "Custom", each [Column1]=1),
#"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Custom.1", each [Custom]),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Hinzugefügte benutzerdefinierte Spalte1",{{"Custom.1", Int64.Type}})
in
#"Geänderter Typ"


Hope this helps

Uwe
 
UK_GER's suggestion to format the column as a number is probably the easiest, but you can also wrap your Text.Contains with Number.From

Code: 
=Number.From(Text.Contains([additives],"Bupr"))

This would be helpful if you needed the 0/1 numbers for additional math in the same column.
 
You must log in or register to reply here.
Back
Top