Lookup an ID ...is the name contained in?

[Netrelemo]

I am struggling to do a lookup. This article is not quite helpful Solved: Re: How to lookup values in another table in the Q... - Microsoft Fabric Community, so...

I am trying to match two tables. 

Table1 has a FolderID, and FolderSources

Table 2 has a FolderName. 

In Table2, I want to see if Table1.FolderSources contains FolderName. If it does, then return the FolderID. If it doesn't then return null. 

This would be easy, but it doesn't work. 

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Table1][FolderSources], [FolderName]) then [Table1][FolderID] else null)

Here is Table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY3LCsIwEEV/Zci6O9NUyEoRl1VswUXpYmyDDZVJyMPvdxKZxYU5l3OnSRxUJxpx8v5jNNwC0pvzjMQn5oa5bBUXHm7ZNdzRm6BhWGyMLsRaUN2xGAazImkYQy7FMaCliqWUTC/OvQyLOf/fVsk63OcUNfTmW7xXl2mtvCqfG6YCIG02guW5+Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FolderID = _t, FolderSources = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FolderID", Int64.Type}, {"FolderSources", type text}})
in
    #"Changed Type"

And here is Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsSC1S0lFSitWJVgopKk3OhnFc8vNhTL/UMoQi/6LEvPRUGM+xoCAHwokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FolderName = _t, LookupFolderID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LookupFolderID", Int64.Type}})
in
    #"Changed Type"

What is the formula to find the Folder ID? 

[Ashish_Mathur · Accepted solution]

Hi,

An easy solution is to split the entries in the FolderSources Column of Table 1 into rows.  Then merge the two tables. 

[v-kongfanf-msft]

Hi @Netrelemo ,

You can try below m function to get folder files.

Folder.Files(path as text, optional options as nullable record) as table

Also, you can refer to below blog:

Get the List of FOLDERS only in Power BI using Power Query - RADACAD

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Netrelemo]

Thanks but these are not "folders" in the traditional file strorage sense.