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 creat...
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

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

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

        Hello

         

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

        Tell me if you need help to implement it.

         

        Bye

         

        Jimmy