Forum Discussion

primolee's avatar
primolee
Icon for Helper V rankHelper V
6 years ago
Solved

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

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
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

7 Replies

    • primolee's avatar
      primolee
      Icon for Helper V rankHelper V
      6 years ago

      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's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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