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.
That sounds way slower than it should be for that. What transformations are you doing in the Transform from example function/query? Are you doing multiple merges in the transform example or the final query? If you share your M code, we can help troubleshoot the slowness. Copy/paste from the advanced editor.
Pat
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
