Forum Discussion
Power Query folder combine -- file combining -- faster?
peterhui50 can you not use this:
=List.ContainsAny({"A".."Z"}, Text.ToList([Column1]))It returns true or false. It is case sensitive. So the "xxxxx" value returns false since it isn't in A-Z
Changing it to this returns Yes or No.
if List.ContainsAny({"A".."Z"}, Text.ToList([Column1])) then "Yes" else "No"And changing it to this is case insensitive.
if List.ContainsAny({"A".."Z", "a".."z"}, Text.ToList([Column1])) then "Yes" else "No"peterhui50 - post back with performance enhancements. I don't know how much more efficient a List.Contains with Text.ToList is vs a single if/then/else with Text.Contains, but it surely must be more efficient than 26 of them!
Thanks to mahoneypat for asking the right question. I was just thinking originally of a lot of data. Looking at the transformations should have been at the top of my list as well.
Thank you so much,
I don't know if I can even fit the code on to here, mostly because of the columns, but further inspection, there are no merges. However, there are a lot of conditional steps -- here is just one of them, I think there are 4 more steps that are like this as well. Is there a way to do this simpler?
#"Added Conditional Column1" = Table.AddColumn(#"Inserted First Characters", "Files_Code contains text", each if Text.Contains([FILES_CODE], "A") then "YES" else if Text.Contains([FILES_CODE], "B") then "YES" else if Text.Contains([FILES_CODE], "C") then "YES" else if Text.Contains([FILES_CODE], "D") then "YES" else if Text.Contains([FILES_CODE], "E") then "YES" else if Text.Contains([FILES_CODE], "F") then "YES" else if Text.Contains([FILES_CODE], "G") then "YES" else if Text.Contains([FILES_CODE], "H") then "YES" else if Text.Contains([FILES_CODE], "I") then "YES" else if Text.Contains([FILES_CODE], "J") then "YES" else if Text.Contains([FILES_CODE], "K") then "YES" else if Text.Contains([FILES_CODE], "L") then "YES" else if Text.Contains([FILES_CODE], "M") then "YES" else if Text.Contains([FILES_CODE], "N") then "YES" else if Text.Contains([FILES_CODE], "O") then "YES" else if Text.Contains([FILES_CODE], "P") then "YES" else if Text.Contains([FILES_CODE], "Q") then "YES" else if Text.Contains([FILES_CODE], "R") then "YES" else if Text.Contains([FILES_CODE], "S") then "YES" else if Text.Contains([FILES_CODE], "T") then "YES" else if Text.Contains([FILES_CODE], "U") then "YES" else if Text.Contains([FILES_CODE], "V") then "YES" else if Text.Contains([FILES_CODE], "W") then "YES" else if Text.Contains([FILES_CODE], "X") then "YES" else if Text.Contains([FILES_CODE], "Y") then "YES" else if Text.Contains([FILES_CODE], "Z") then "YES" else "NO"),
I think this can be made much faster. Two things.
1. Try removing your custom column and see how fast the data load. If that alone takes a long time, a different fix is needed.
2. Try this approach instead for your custom column. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs7PTVUoSa0oUYrViVbKL8lILUJwE/MgAvl5qWB+RWWVUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Files_Code = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Files_Code", type text}}),
MyList = List.Buffer({"a", "b", "c", "d", "e"}),
Custom2 = #"Changed Type",
#"Added Custom" = Table.AddColumn(Custom2, "Custom", each let mytext = [Files_Code] in if List.Count(List.Select(MyList, each Text.Contains(mytext, _)))>0 then "Y" else "N")
in
#"Added Custom"
I made up some sample data, made the list of letters/words to look up in the MyList step (note that M is case sensitive, so you may want to make your Files_Code column UPPER or lower case before this step. It then counts how many items in the list are found in your Files_Code and returns Y if any are found. Please report back if it improves things.
Pat