Make a list of function names into a record with Function values

[primolee]

Hello everyone,

Currently I am making a record of my functions manually.
[
A = A,
B = B,
C = C,
]

However, I will keep on making more functions so I was wondering if there is a way to create the above record dynamically without using #shared.

I created a list with all function names and then used Record.FromList, but I am not able to make values into Function.

Is it possible?

Thank you very much.

Best regards,
David

[v-frfei-msft]

Hi @primolee ,

Please refer to this article.

https://www.poweredsolutions.co/2019/02/19/parameters-and-functions-in-power-bi-power-query-custom-f...

[primolee]

Hello,

Thank you for your reply.  The link shows how to invoke a function using pre-made functions.  What I am looking for is different.

I have .csv/.xlsx reports coming from many different sources, so I have to make custom functions to invoke them accordingly.  Because number of sources will keep on increasing, I need to build more and more custom functions in the future.

Depending on source name, I will use corresponding custom function.  Therefore, this is what I have now.

functionRecord = [
        dataSource01 = dataSource01,
        dataSource02 = dataSource02,
        dataSource03 = dataSource03,
    ],
#"Invoke Custom Function" = Table.AddColumn(#"Previous Step", "Processed Tables", Record.Field(functionRecord, [columnSourceName])([Folder Path]&[fileName]),

This codes assume that my custom function name is the same as data source name.

Every time when there is a new source, I need to add a new record into functionRecord.  I could simply replace functionRecord with #shared, but #shared does not work in Power BI Service.  This is why I pre-build a functionRecord.

Since I have a list of all my source names, I am looking for a way to turn this list into the record I mentioned above.

Now I have nearly 40 custom functions now, and I am writing 40 lines manually...

functionRecord = [
        dataSource01 = dataSource01,
        dataSource02 = dataSource02,
        dataSource03 = dataSource03,
        .....,
        .....,
        dataSource40 = dataSource40,
    ],

Hope there is a way to do so, or I will simply maintain this record manually. 😛

Thank you. (^_^)

Best regards,

David

[Jimmy801 · Accepted solution]

Hello @primolee ,

are these data sources (xlsx) so different that your are not able to create one function to combine them?

However... this example should help you to create a record based on a Table/List. In my example i have a table with column function name and function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNTUot0nMrys9V0kHhxepEK4WkVpTA5BDs2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Function name" = _t, Function = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Function name", type text}, {"Function", type text}}),
    ChangeToFunction = Table.TransformColumns(#"Changed Type", {"Function", each Expression.Evaluate(_, #shared)}),
    CreateRecord= List.Last(List.Generate
    (
        ()=>
        [
            FinalRecord = [],
            Counter = 0
        ],
        each [Counter]<= Table.RowCount(ChangeToFunction),
        (x)=>
        [
            FinalRecord = Record.AddField(x[FinalRecord], ChangeToFunction[Function name]{x[Counter]}, ChangeToFunction[Function]{x[Counter]}),
            Counter = x[Counter]+1
        ],
        each [FinalRecord]
    ))
in
    CreateRecord

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[primolee]

Hello Jimmy,

I will try it tomorrow as I don’t have my computer with me.

And yes, all my data sources are very different. I work in a media agency handling media data source from different media providers such as Yahoo, Google and Facebook who can be pretty standard, but local media providers provide very different format from one other.

We are trying to standardize them in the future, but meanwhile.... (sweat)

[Jimmy801]

Hello
are there any news on this topic? Did solve or help any reply your problem?
If this is the case, please mark it as solution.

Jimmy

[primolee]

Sorry, was busy at something else and forgot about this.

Just gave it a try, it will work but #shared is still used.  This will not work in Power BI Service.

I guess there is no way to define type "Function" any other way...  But thanks for the help! 🙂

[Jimmy801]

Hello

okay.. .give it a try and give feedback.

Tell me if you need help to implement it.

Bye

Jimmy