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.Type}})

 

Thank you for every suggestion!

Melanie

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

6 Replies

  • 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