Directories at different levels are critical for some organisations. One of the requirements I received recently was to import Flat Files from directories ( Hundreds of them ) at different levels. Apparently Power BI makes it easy to import files from a folder, however in my case, I needed to choose the files to import based on the folder level and many other conditions which are also related to the level of the folder and file names. Folder level was a key, so let’s see how to find folder level:
When we choose Folder as a data source in Power BI, it imports some metadata along with file content. For my requirement, I used Folder Path column to find the level. In the Folder Path, if I count the number of backslashes (), that will give me the level of the folder.
Instantaneously I started using List.FindText. First I converted Folder Path column to a list, then I used List.FindText to find the position of “\”, then List.Count function to find no of items in the list. It works but notably slow. What I wanted was a Text function than List Functions.
Even though the description was a bit misleading in the documentation, I decided to look into Text.PositionOf. Like many Power Query functions, this function also has optional arguments like Occurrence and comparer. When we use Text.PositionOf with no occurrence, it returns a number. But if we want all occurrences of a substring, then it returns a list.
For my scenario, I just needed no of occurrences, so I used List.Count to find no of items in the List. And this approach was significantly faster than using List.FindText.
At the end I used List.FindText to find the level, in my real example, I used a lot more custom filters to fulfil my requirement. Here are the formulas I used:
Using List.FindText – List.Count(List.FindText(Text.ToList([Folder Path] as text) as list, “\” as text) as list) as number
Using Text.PositionOf – List.Count(Text.PositionOf([Folder Path],”\”, Occurrence.All) ) as number
Summary: Hope this helps, one thing I learned working with Power Query is “Most of the functions has more functionality than I presume, and it often hidden under optional commands “
Prathy ♀️
