Forum Discussion

MBreden's avatar
MBreden
Helper I
6 years ago
Solved

ColumnNames with Datatypes

Hi all,   how can I combine dynamic ColumnNames with datatypes?   For example: = Table.TransformColumnTypes(#"Ersetzter Wert",{{"FAP", Currency.Type}, {"BLP", Currency.Type}, {"Sales", Int64.Ty...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello MBreden  Melanie

     

    find herewith enclosed an example how this could work out. The easy task to extract the columnnames, but I don't know how you thought how to detect the correct type for every column. But maybe you get an idea how it could work out

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTKxMDQyBtKmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, Currency = _t]),
    ColumnNames = Table.ColumnNames(Source),
    Types = {type number, Int64.Type, Currency.Type},
    Zip = List.Zip({ColumnNames, Types}),
    AdaptTypes = Table.TransformColumnTypes(Source, Zip)
in
    AdaptTypes

     

    Copy paste this code to the advanced editor to see how the solution works

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

    Jimmy

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Helllo MBreden 

 

You can create a nested list with ColumnName and data type in it. If you can prepare such a list you can hand it over to the function in your example. Or It's your question more like "how do I identify dynamically the data type of my column?"


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

Jimmy

  • MBreden's avatar
    MBreden
    Helper I
    6 years ago

    Hello Jimmy,

     

    thank you for your answer!

    Just right, I need a nested list of ColumnNames and data types to insert into the function.
    But I don't know how to create it ğŸ™„
    Can you give me an example?

    Melanie

    • Jimmy801's avatar
      Jimmy801
      Community Champion
      6 years ago

      Hello MBreden  Melanie

       

      find herewith enclosed an example how this could work out. The easy task to extract the columnnames, but I don't know how you thought how to detect the correct type for every column. But maybe you get an idea how it could work out

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTKxMDQyBtKmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, Currency = _t]),
    ColumnNames = Table.ColumnNames(Source),
    Types = {type number, Int64.Type, Currency.Type},
    Zip = List.Zip({ColumnNames, Types}),
    AdaptTypes = Table.TransformColumnTypes(Source, Zip)
in
    AdaptTypes

       

      Copy paste this code to the advanced editor to see how the solution works

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

      Jimmy

      • MBreden's avatar
        MBreden
        Helper I
        6 years ago

        Thanks Jimmy,

         

        I will try to adapt the code to my query

         

        Melanie