How to do conditional VLOOKUP in power query

[Anonymous]

Hey experts, 

This is my second part of the question to create a new query from the given query full of M functions  "=>" as hyperlinks. 

Example: new query - > =#shared 

Now I have a list functions in my excel as a seperate query, I want to pull all the list elements 

Name
List.Alternate
List.Accumulate
List.AllTrue
List.AnyTrue

I want to lookup each of the above and pull from the query output of =#shared rows that are ONLY functions and I want to copy entire contents including the query functions. 

What I tried :

I tried using joins, then I get the following error 

Formula.Firewall: Query 'Query1 (2)' (step 'Converted to Table') is referencing Query 'AllFunctions' (step 'Changed Type'), which was not part of its formula text.

then I googled,  to find list.positionof but I have no clue how to get two seperate queries columns into one custom function to create the formula.

2. When I used VLOOKUP to pull the data, query functions did not come through. 

Please can you help.

Regards

[Anonymous]

@lbendlin Thanks a lot.

Just to clarify, I have taken this an example as my work data is confidential, unfortunately. 

My work file has a lot of functions similar to above, => and linked to several queries, like #shared 

The reason, when I do vlookup or Table [column]{List.PositionOf ( )} is I get the value and underlying functions are not copied so I lose the links. Eg. The [Table], [Function] are hyperlinks in query editor and click on them, I can invoke the functions. It would be very easy, If I can extract dependencies and functions as a hyperlink when I do a excel vlookup or Table [column]{List.PositionOf ( )}. 

I have some set of functions, for which I need to extract these links. 

PS: Failing in the above, I used excel hyperlinks where there was a function mapped and about to give this back to dev for the reference.  Did I make sense at all? Apologies, If not when I am back home, I will upload with screenshots.  Many thanks.

So I found #shared as an perfect example, if I can get solution there, I can get solution to my work. 

Bit about what I tried to achieve:

1. There is huge library of reference eg. #shared.

2. I get a requirements list to implement for a list from vendors, I have to get back if there is already available APIs and function, that is example in eg. #Shared

Name
List.Alternate
List.Accumulate
List.AllTrue
List.AnyTrue

List.Draw

3 . Now for most of the functions above there is already available implementable functions in #Shared. but there is none for List.Draw.  So my job is where it is available, I will give back a API reference in our eg. [Function] in shared.

List.Draw I will have to expand, create requirements and put in a dev sprint. 

4. Problem is when I extract I lose clickable [Function] functionality.

[lbendlin]

Doesn't make sense to me. You can prepare sample data / code that emulates your functions. Please no screenshots.

[lbendlin]

and I want to copy entire contents including the query functions. 

Please elaborate, not clear what this means. Are you looking to retrieve the documentation meta data?

Here is the basic code to filter for functions.

let
    Source =  Record.ToTable(#shared),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Value.Is([Value], type function)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true))
in
    #"Filtered Rows"

What do you want to do next?

[Anonymous]

Yes @lbendlin  you got it right. All I want is meta data as well, so the user can click and get details. This is just a thought i have to make it better, this is not the only way. 

The metadata as in 

Web.Page -- > [Function] (html) as table => ... -- I want to extract (html) as table => ...
List.Range -- > [Function]  but that has a link and want that has this --> (list as list, offset as number, optional count as nullable number) as list => ... 
Similarly for List.Accumulate -- > (list as list, seed, accumulator as function) => ... and 
List.AllTrue -- > (list as list) as logical => ...

Did I give details, that can get me help ?

[lbendlin · Accepted solution]

Power Query M Primer (Part 20): Metadata | Ben Gribaudo

[Anonymous]

@lbendlin Thanks a lot. I am learning from that link, I am in lessson 2. 🙂 but this is very uselful to me.

[lbendlin]

In my opinion that series is by far the best way to learn Power Query. Combine it with the Guide to DAX by sqlbi.com to cover the majority of Power BI.

[Anonymous]

Thank you @lbendlin this helps me a lot.