trying to use a query step with a function as a stand-alone

[monojchakrab]

Hiya all,

I have a step inside a query as follows :

let
    Source = Table.Combine({#"Alok_2022",#"Homanjit_2022",#"Karthi_2022",#"Jitender_2022",#"Vijay_2022"}),
    #"Replaced Value" = Table.ReplaceValue(Source,"Equal Sucrlose 100 sachet","Equal Sucralose 100 sachet",Replacer.ReplaceText,{"Product Description"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Ingredient", each if Text.Contains([Product Description],"sucralose",Comparer.OrdinalIgnoreCase) then 
"Sucralsose" else 
if Text.Contains([Product Description],"aspartame",Comparer.OrdinalIgnoreCase) or 
Text.Contains([Product Description],"original",Comparer.OrdinalIgnoreCase) then 
"Aspartame" else
if Text.Contains([Product Description],"stevia",Comparer.OrdinalIgnoreCase) then 
"Stevia" else "C&B"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}, {"Customer Name", type text}, {"Town", type text}, {"Serviced By", type text}, {"Category", type text},
{"Customer Type", type text},{"Customer Segment", type text},{"Product Description", type text},{"Units Sold", Int64.Type},{"Ingredient", type text},{"Region", type text}
}),
    SpellCorr = Table.ReplaceValue(#"Changed Type","Sucralsose","Sucralose",Replacer.ReplaceText,{"Ingredient"}),
    RateIndex = Table.AddColumn( SpellCorr, "Rate Index", each List.PositionOf(RateMaster[Ingredient],[Ingredient])),
    #"Filtered Rows" = Table.SelectRows(RateIndex, each true),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Billing Rate", each RateMaster[Price]{[Rate Index]}),
    RateAnotherWay = Table.AddColumn(#"Added Custom1", "Rate_V1",
                                    (TargetTable)=>
                                        Table.SelectRows(RateMaster,
                                                                (SourceTable)=>
                                                                    Text.Contains(TargetTable[Ingredient],SourceTable[Ingredient]))),
    #"Expanded Rate_V1" = Table.ExpandTableColumn(RateAnotherWay, "Rate_V1", {"Price"}, {"Price"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rate_V1",{{"Rate Index", Int64.Type}, {"Billing Rate", type number}, {"Price", type number}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Query1", each Query1([Ingredient]))
in
    #"Invoked Custom Function"

I am trying to replicate the step RateAnotherWay as a stand alone function to be used with other tables as tried to write a query as follows :

let 
RateAnotherWay = (TargetTable as table )=>
                    Table.SelectRows(RateMaster,
                            (SourceTable)=>
                            Text.Contains(TargetTable[Ingredient],SourceTable[Ingredient]))
in 

RateAnotherWay

But this is not returning the expected result - rather it returns an error saying cannot convert value of type list to text or something to that effect.

Am I missing something here? Any hack to get around this problem will be highly appreciated. What flummoxes me is that while the line of code works within the other script it does not work as a standalone function

Thanking in anticipation

[nitishsh91]

Hi @monojchakrab 

It looks like the issue might be that in the standalone function, the TargetTable[Ingredient] is returning a list of values, while in the original script it is returning a single value.

You can try changing the Text.Contains function to List.Contains to check if the Ingredient value is present in the list instead of a single value.

Try modifying the RateAnotherWay function as follows:
let
RateAnotherWay = (TargetTable as table )=>
Table.SelectRows(RateMaster,
(SourceTable)=>
List.Contains(TargetTable[Ingredient],SourceTable[Ingredient]))
in

RateAnotherWay

Another thing you can try is to get the first element of the list using List.First before passing it to the Text.Contains function.

let
RateAnotherWay = (TargetTable as table )=>
Table.SelectRows(RateMaster,
(SourceTable)=>
Text.Contains(List.First(TargetTable[Ingredient]),SourceTable[Ingredient]))
in

RateAnotherWay

It's also possible that the column Ingredient in the TargetTable is not a single value column, and it could be a list. In that case, you should first try to extract the single value from the list using the List.First function before using it in the Text.Contains function.

Please let me know if this works for you or if you have any other questions.

[monojchakrab]

@nitishsh91 - Thanks for the quick revert.

I tried both the solutions :

#1 : returns just the RateMaster table

#2 : just returns the 1st item from the list :

The Ratemaster table looks like this :

And solution 2, returns this as the result of the invoked function :

This is probably the expected behaviour - but I am not sure how do I run this function to add a column in the TargetTable, holding a table with each of these records, whereever the TargetTable[Ingredient] matches SouceTable[Ingredient]?

When I ran this from within the script - this is what I was getting :

Then I expanded the table and simply kep the Price column in the expanded table step.

Is there a way to do the exact same with the fucntion now sitting outside the query script?

Thanks